Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    VBA to open multiple exel documents, update, save, and close them.

    Hello,

    I'm a little new to VBA. I currently have a VBA script in MS Access that automatically opens fourteen excel documents that our team needs to update on a daily basis for linking. We used to have to open each individual document, update linking upon opening the doc, then save and close the documents one at a time.

    Below is my current vba. It successfully opens each xls document one at a time automatically. After I manually update/save/exit each xls document, the next xls document in the series will then be opened for updating.

    What I'm trying to do now is add code to automatically update each excel document, save the document and close the document before the next document is automatically opened. Is this possible?
    Thanks in advance...




    Private Sub Command47_Click()

    ' Late Binding (Needs no reference set)
    Dim oXL As Object
    Dim oExcel As Object
    Dim sFullPath As String
    Dim sPath As String


    ' Create a new Excel instance
    Set oXL = CreateObject("Excel.Application")


    ' Only XL 97 supports UserControl Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0


    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/California.xls"




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/capital.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Crossroads.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Denver.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Florida.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Midwest.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/nashville.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/NewJersey.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/NewYork.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Northeast.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Northwest.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/SES.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Southwest.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)
    End With

    ' Full path of excel file to open
    On Error GoTo ErrHandle
    sFullPath = "\\ad\xx\xxxxx\Reports/Daily Calibration Call Sheets/Weekly Segmentation Matrix/Current Week/Texas.xls "




    ' Open it
    With oXL
    .Visible = True
    .Workbooks.Open (sFullPath)


    ErrExit:
    Set oXL = Nothing
    Exit Sub

    ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit



    ' Create a new Excel instance
    Set oXL = CreateObject("Excel.Application")


    ' Only XL 97 supports UserControl Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
    End With


    End Sub

  2. #2
    Join Date
    Oct 2012
    Posts
    2
    Should I repost this question in a different area of dBforums? It looks like this might not be something that can be done since no one responded.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •