I have been developing a Access Db that should be able to
manipulate, and use, data from Excel worksheets with
multiple tabs (sheets). I am currently attempting to use
a Access form that allows the user to enter the Excel
spreadsheet's name and location. Using "GetObject", I
have been able to select a specific tab on the Excel
spreadsheet. I then make a copy of that specific tab and
save that tab as a new Excel document using the
following code :
Dim excelAppObject As Object
Dim excelAppParent As Object
Set excelAppObject = GetObject(fileLocation)
Set excelAppParent = excelAppObject.Parent
excelAppObject.Sheets(copySheet01).Select
excelAppObject.Sheets(copySheet01).Copy
excelAppParent.ActiveWorkbook.SaveAs FileName:= _
fileCopyLoc & "\Sheet01.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
That portion works fine and a new Excel document is
created, however the Access Db should also allow the
coping of many Excel sheets (tabs) and make additional
Excel spreadsheets.
I tried to do this below to select another tab from the
same Excel spreadsheet and create another Excel document.
excelAppObject.Sheets(copySheet02).Select
excelAppObject.Sheets(copySheet02).Copy
VB give me the following error:
"Run-time error '1004':
"Selected method of Worksheet class failed"
How can I copy multiple tabs from a Excel Spreadsheet and
save them as individual Excel Spreadsheet..I am stuck!
Any feedback would be appreciated. Thanks
|
Similar Threads
