Excel/Power point VBA

 

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

Top

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

Top

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"


Top

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

Top

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

Top

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

Top

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

Top

 
www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.