Excel VBA Utilities


  • Refresh pivot table by refreshing its cache :-
                ActiveWorkbook.PivotCaches.Item(1).Refresh

  • Copy data from recordset to excel :-
                ActiveSheet.Range("A2").CopyFromRecordset RS

  • Check whether an Excel sheet exists in given workbook or not:-
               Function SheetExists(shtName As String) As Boolean

                     Dim sht As Worksheet
    
                     On Error Resume Next
                     Set sht = xlWrkBk.Sheets(shtName)
                     On Error GoTo 0
                     SheetExists = Not sht Is Nothing
               End Function

Here xlWrkBk is the Excel workbook in which we are searching for Excel worksheet with its name.


  • Check whether the workbook is open or not:-
               Function Isworkbookopen(filepath As String)
                    Dim ff As Long, ErrNo As Long
                    Dim wkb As Workbook
                    Dim nam As String
    
                    'wbname = filepath
                    On Error Resume Next
    
                    ff = FreeFile()
                   Open filepath For Input Lock Read As #ff
                   Close ff
                   ErrNo = err
                   On Error GoTo 0
                   Select Case ErrNo
                        Case 0: Isworkbookopen = False
                        Case 70: Isworkbookopen = True
                        Case Else: Error ErrNo
                  End Select
            End Function

Here filepath  is the complete path of the excel workbook that you want to check whether it is open or not.


  • Change the path of workbook to which current workbook is linked.
               ActiveWorkbook.ChangeLink "c:\excel\book1.xls", "c:\excel\book2.xls", xlExcelLinks

  • Update the data of workbook from the linked workbooks:-
               ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources(xlExcelLinks)
               ActiveWorkbook.RefreshAll

Here  ActiveWorkbook.RefreshAll will refresh the data of all the worksheets and pivot tables in the workbook.

  •  Disable Autorecover of whole excel application :-
               ActiveWorkbook.Application.AutoRecover.Enabled = False

  • Move a sheet before another in an excel workbook :-
              ActiveWorkbook.Sheets("Sheet9").Move before:=ActiveWorkbook.Sheets("Sheet1")

  • Move a sheet after another in an excel workbook :-
              ActiveWorkbook.Sheets("Sheet1").Move after:=ActiveWorkbook.Sheets("Sheet9")

  • Freeze rows and columns of an excel sheet:-
               With xlWrkSht.Application.ActiveWindow
                   .SplitColumn = 1
                   .SplitRow = 3
                   .FreezePanes = True
               End With

Above code will freeze 1 column and 3 rows.

  • Apply autofill in excel :-
              ActiveSheet.Range("C7").AutoFill Destination:=Range("D7:N7")