If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Visual Basic > VBA to open multiple exel documents, update, save, and close them.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Tags
excel, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On