I am trying to open an excel workbook, then copy a sheet to another workbook.
I am confused as to what Dim and Set variables to use for each workbook, and how to reference them.
Currently I have the following code:
Dim oXl As Excel.Application
Dim aBook As Workbooks
Set aBook = oXl.Workbooks
aBook.Open InputFileName
Workbooks(aBook).Sheets("R
This is not working - I'm sure because I have not set it up correctly...
Thanks you for your help,
athemelis.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi WATYF,
I'm actually in Access, writing code that manipulates data I have transferred to excel.
I tried implementing something similar to what you suggested, but it still doesn't work:
Dim oXl As Excel.Application
oXl.Workbooks.Open InputFileName
oXl.Workbooks.Open OutputFileName
oXl.Workbooks(InputFileNam
oXl.Workbooks(InputFileNam
oXl.Workbooks(OutputFileNa
athemelis
Well... couple things.
1) If you're in Access, then you have to add the Excel library to the project before you can reference Excel code. Menu option Tools > References. Scroll down to Microsoft Excel 9.0 Object Library (9.0 could be different based on your version of Excel). Select it and hit OK.
2) You can't use the full path and file name to reference the Workbooks object. That is, when opening the file, you need the full name AND path, but when calling the workbook, you just need the name, not the path. So you can't use the same variable for both operations. Create a variable that contains the path, so you can use the file name by itself. Or, if there's no way change how the variables are qualified, then you'll have to get the filename from the variable before using it in the Workbooks method.
So here's one option: (where FilePath = the path where the files are, and the FileName variables just equal the name of the file)
Sub Test()
Dim oXl As Excel.Application
Set oXl = CreateObject("Excel.Applic
oXl.Visible = True
oXl.Workbooks.Open FilePath & InputFileName
oXl.Workbooks.Open FilePath & OutputFileName
oXl.Workbooks(InputFileNam
oXl.Workbooks(InputFileNam
oXl.Workbooks(OutputFileNa
End Sub
Or, you can parse the variable to get the filename:
Sub Test()
Dim oXl As Excel.Application
Set oXl = CreateObject("Excel.Applic
oXl.Visible = True
oXl.Workbooks.Open InputFileName
oXl.Workbooks.Open OutputFileName
InptFl = Mid(InputFileName, (InStrRev(InputFileName, "\") + 1), Len(InputFileName))
OtptFl = Mid(OutputFileName, (InStrRev(OutputFileName, "\") + 1), Len(OutputFileName))
oXl.Workbooks(InptFl).Shee
oXl.Workbooks(InptFl).Clos
oXl.Workbooks(OtptFl).Clos
End Sub
The last method would have the least impact on your current process, because you could still qualify the variables the exact same way.
WATYF
Business Accounts
Answer for Membership
by: WATYFPosted on 2003-07-22 at 12:44:38ID: 8979777
Well if you're working in Excel, you don't have to Dim all that. And the file has to be open the have the sheet copied into it.
Sub Test()
Workbooks.Open InputFileName
Workbooks.Open OutputFileName
Workbooks(InputFileName).S
Workbooks(InputFileName).C
Workbooks(OutputFileName).
End Sub
WATYF