Create a new Excel File in VBA

[chacal]

Hi everyone,

My problem is the following:

I execute some queries, and at the end, I have to transfer the result in an Excel file that is not yet created. Here is my problem. I don't know how to create a new Excel file in VBA. I know how to open an existing one, but not creating one.
I tried

ActiveControl.Hyperlink.CreateNewDocument FilePath, False, False

and that works well in Access 2003, but when I'm testing my program in Access 2002, It gives me the following error:

Error -2147024893 The Method 'CrreateNewDocument' of the Object '_Hyperlink' failed

I'm really confused. Thanks in advance for your help.

 

[KenHigg]

I generally use the OutPutTo method:

DoCmd.OutputTo acOutputTable, "Employees", acFormatRTF, "Employee.rtf", True

 

[chacal]

ok,

But I think it won't works for me, because I have to do some manipulation within it. That's why I cannot use that.

 

[M8KWR]

ok,

But I think it won't works for me, because I have to do some manipulation within it. That's why I cannot use that.

why do you feel you can not use this method????

 

[Brianwarnock]

Never ever done this but popped into one of my databses and tried it bur using xls where Ken had rtf and got myself a spreadsheet. Seems dead simple.

Brian

 

[chacal]

It is in fact quite simple, but in my case, I have to work with more than one worksheet (3 actually), and with the docmd.output method, it will create only one. do you think there could be a way to create more than one worksheet with the docmd.output?

 

[KenHigg]

Hum...

I wonder if you do up a sample .xls with the 3 worksheets. Then attach to each as 3 seperate tables. Next, manipulate the data as needed. So when you need to share the the data, simply forward a copy of the .xls?

 

[chacal]

This could be a possibility...
May be I should create a template, and use it to create the file I want.

I'll try that.

 

[ghudson]

If you use the transfer spreadsheet method to output your data [query or table] you can have each output saved to a different worksheet as long as you output to the same file name. The sheet name will be based on the name of your query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "X:\Test\File.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query2", "X:\Test\File.xls", True

 

[chacal]

all right,
I manage to create the file.
I first used the docmd.outputto method, and then reopen the file and adding to more worksheets.

Thanks everyone for your help!
I appreciate

 

[KenHigg]

If you use the transfer spreadsheet method to output your data [query or table] you can have each output saved to a different worksheet as long as you output to the same file name. The sheet name will be based on the name of your query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "X:\Test\File.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query2", "X:\Test\File.xls", True

Cool - I did not know that!