Creating and populating a new Excel file through Access VBA

[emik]

Hi,

Here is what I'm trying to do. I have 6 queries and I need to output the data to the same Excel sheet with calculations that I will do in Access. So essentially I want to create an Excel file, write my values to it and then save it.

I know the starting part would be something like:

Dim xlApplication As Excel.Application
Dim xlWorkbook As Excel.Workbook
Set xlApplication = New Excel.Application

Once I have the general syntax of how to do it I can play around with the code and transferring data from my recordsets should be straight forward (albeit a lot of coding).

Thanks for any help.

 

[PHV]

So, where are you stuck ?
Tip: have a look at the CopyFromRecordset method of the Excel.Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Check out the CreateObject method in VBA Help.

Create a new workbook in the xlApplication object.

Add a new worksheet in the xlWorkbook object.

From there its just manipulating the various Access and Excel objects.

I'd also do some coding in Excel VBA, as you have the functionality to RECORD macros, observe the code and then modify to suite your Access VBA environment.



Skip,
[sub]
[red][/red]
[/sub]

 

[emik]

Thanks for the advice, I'll check it out. Some priorities came in though so I probably won't be able to get around to it until then.

Thanks guys.

 

[emik]

I hate not being able to modify, I meant until Monday