Transfer data to excel spreadsheet, MS Access 2003 VBA
I just want to know the vba code to create multiple tabs in a spreadsheet and put data in each tab
So, it would be create tab transfer data then create another tab and transfer data and so on...
I know how to open a excel spreadsheet and transfer data to one tab only using vba in ms access 2003.
So, it would be create tab transfer data then create another tab and transfer data and so on...
I know how to open a excel spreadsheet and transfer data to one tab only using vba in ms access 2003.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
using
Set objExcelWorkBk = objExcel.Workbooks.Open(..
Set objExcelWorkBk = objExcel.Workbooks.Open(..
So that tells us how you are opening a workbook but not how you are transferring the data. Look at fyed's example; that should get you started.
OM Gang
OM Gang
ASKER
just doing something like this
objExcelWorkBk.Worksheets(
objExcelWorkBk.Worksheets(
asarda,
Have you tried to look at my code and understand how it works. It really is simple to add new worksheets and fill them with data.
When you reply, it makes it easier on those of us that are trying to help if you would identify who you are replying to.
Have you tried to look at my code and understand how it works. It really is simple to add new worksheets and fill them with data.
When you reply, it makes it easier on those of us that are trying to help if you would identify who you are replying to.
Dim xl As Excel.Application
is coming up as invalid. It doesn't seem to recognize Excel....... when DIMing.
is coming up as invalid. It doesn't seem to recognize Excel....... when DIMing.
To early bind you'll need to set a reference to the Excel object library.
OM Gang
OM Gang
I'm sorry, how do I reference the Excel object library?
In the VBE (VBA editor) select the Tools menu then References. Scroll through the list to find Microsoft Excel 12.0 Object Library and check the box next to it.
12.0 is Excel 2007. If you have 2003, it'll be the Microsoft Excel 11.0 Oject library. Etc.
OM Gang
12.0 is Excel 2007. If you have 2003, it'll be the Microsoft Excel 11.0 Oject library. Etc.
OM Gang
Sorry, forgot about the reference.
I always use early binding in the development phase, then go back and change all of the declarations of those to:
Dim xl As Object 'Excel.Application
Dim wbk As Object 'Excel.Workbook
Dim sht As Object 'Excel.Worksheet
When you use early binding (as mentioned by omgang) you get the benefit of intellisense, so that when you type the dot (.) after an object, you get the list of its properties and methods. But once you are done with development, it is ideal to go back and change these declarations to Object and remove the reference to the particular object libraries (Excel, Word, PowerPoint, Outlook, ...). This way, the code is less dependant on a particular version of Office being installed on the users machine.
I always use early binding in the development phase, then go back and change all of the declarations of those to:
Dim xl As Object 'Excel.Application
Dim wbk As Object 'Excel.Workbook
Dim sht As Object 'Excel.Worksheet
When you use early binding (as mentioned by omgang) you get the benefit of intellisense, so that when you type the dot (.) after an object, you get the list of its properties and methods. But once you are done with development, it is ideal to go back and change these declarations to Object and remove the reference to the particular object libraries (Excel, Word, PowerPoint, Outlook, ...). This way, the code is less dependant on a particular version of Office being installed on the users machine.
Sorry, this must have falled off my radar. Fyed's response was helpful. I would like to close it out and give him credit.
ASKER
I am ok with giving credit to Fyed
How are you doing it now? Via the TransferSpreadsheet method?
OM Gang