How do I open, save, save as and close an Excel workbook using VBA
'Code by Mahipal Padigela
'Open Microsoft Excel
'Goto VBA Editor (Press Control+F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
'Change the path "H:\F\test1.xls" to suit yours
Sub OpenWorkbook()
Dim wb As Workbook
Set wb = Workbooks.Open("H:\F\test1.xls")
'do your processing......... for example add some text to A10
wb.Sheets(1).Range("A10") = "some text"
wb.Save 'save the workbook
wb.SaveAs "H:\F\test11.xls" 'you do save as like this
wb.Close SaveChanges:=True 'you can also save when closing the workbook
Set wb = Nothing
End Sub
How do I add a chart to Excel worksheet using VBA?
How do I create a chart in excel programatically?
How do I set the properties of Excel chart using VBA? How do I turn on/off legend, gridlines in an Excel chart usinfg VBA?
example uses the following data
'Code by Mahipal Padigela
'Open Microsoft Excel
'Goto VBA Editor (Press Control+F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
Sub addChart()
Dim myChart As Excel.Chart
'add chart
Set myChart = Charts.Add
'set chart type for e.g. xlPie,xlLine,xlArea,xl3DColumn etc
myChart.ChartType = xlColumnClustered
'set data range
myChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D6"), PlotBy:=xlColumns
'Turn on the major gridlines for both axes
myChart.Axes(xlCategory).HasMajorGridlines = True
myChart.Axes(xlValue).HasMajorGridlines = True
'Turn on the Legend and position it on top of the chart
myChart.HasLegend = True
myChart.Legend.Position = xlTop
'Show values on the bars of the chart
myChart.ApplyDataLabels xlDataLabelsShowValue
'Finally, which sheet you want the chart on
myChart.Location xlLocationAsObject, "Sheet1" 'This adds a standard sized chart to sheet2, but if you want to add a
' new sheet with just chart on it, replace the above line with next line...
'myChart.Location xlLocationAsNewSheet, "Mychartsheet"
'If you want specify the height and width of the chart object.......
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft
Set myChart = Nothing
End Sub
How do I protect or lock an Excel worksheet?
How do I protect / lock an Excel worksheet, but still have some
cells or ranges unprotected or unlocked?
To protect a worksheet, use this...
ActiveSheet.Protect password:="testpass", DrawingObjects:=False, Contents:=True, Scenarios:=True
To unprotect a worksheet, use this
ActiveSheet.Unprotect password:="testpass"
To protect a worksheet, but still have some cells or ranges unprotected , first unlock the cells or range
you don't want to protect, then protect the worksheet. the next 2 lines protect the worksheet except the range A10 to D10.
ActiveSheet.Range("A10:D10").Locked = False
ActiveSheet.Protect password:="testpass"
How do I hide/undide an excel worksheet using VBA?
How do I make an excel woksheet visible/invisible using VBA?
To hide(make invisible) a worksheet, use this...
ActiveWorkbook.Sheets("Sheet2").Visible = xlSheetVeryHidden
To unhide(make visible) a worksheet, use this...
ActiveWorkbook.Sheets("Sheet2").Visible = xlSheetVisible
How do I loop through sheets in an excel workbook and
make all hidden / invisible excel sheets visible using VBA?
'Code by Mahipal Padigela
'Open Microsoft Excel
'Goto VBA Editor (Press Control+F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
Sub makeSheetsVisible()
Dim sh As Excel.Worksheet
For Each sh In ActiveWorkbook.Sheets
If sh.Visible = xlSheetHidden Or sh.Visible = xlSheetVeryHidden Then
sh.Visible = xlSheetVisible
End If
Next
Set sh = Nothing
End Sub
How do I find and replace all the entries of a perticular
string in excel using VBA?
To find and replace all entries of a string, use this (substitute "ASD" and "asd111" with your strings).....
Cells.Replace What:="ASD", Replacement:="asd111", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
To just find a string, use this (substitute "ASD" with your string).....
Cells.Find(What:="ASD", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
How do I draw borders around a cell or range in an excel sheet?
'Code by Mahipal Padigela
'Open Microsoft Excel
'Goto VBA Editor (Press Control+F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
Sub DrawBorders()
Dim strRng1 As String
Dim strRng2 As String
strRng1 = "A5:D10" 'set some test range
strRng2 = "C16:G20" 'set some test range
'If you need to draw all the borders with same properties, use this
ActiveSheet.Range(strRng1).BorderAround xlContinuous, xlMedium, 46
'Or if you need to draw the borders individually, applying different properties to each, use this
ActiveSheet.Range(strRng2).Borders(xlEdgeLeft).LineStyle = xlContinuous
ActiveSheet.Range(strRng2).Borders(xlEdgeLeft).ColorIndex = 4
ActiveSheet.Range(strRng2).Borders(xlEdgeTop).LineStyle = xlContinuous
ActiveSheet.Range(strRng2).Borders(xlEdgeTop).ColorIndex = 3
ActiveSheet.Range(strRng2).Borders(xlEdgeBottom).LineStyle = xlContinuous
ActiveSheet.Range(strRng2).Borders(xlEdgeBottom).ColorIndex = 5
ActiveSheet.Range(strRng2).Borders(xlEdgeRight).LineStyle = xlContinuous
ActiveSheet.Range(strRng2).Borders(xlEdgeRight).ColorIndex = 26
End Sub
|
