Exporting Multiple queries to an existing excel file.

[Kmathieu]

Hi All,

I am using the latest MS Access and i am trying to exporting 10 queries to a file that i created using VBA. I would really appreciate the help.

Thanks,
Kalech

 

[Isaac]

Please be more specific, thorough and detailed.

 

[Kmathieu]

Please be more specific, thorough and detailed.

Hi Isaac,

Thank you for taking the time to respond. I have a excel workbook and i am trying to export queries that i have built in MS access to the existing workbook. I would like to export the queries using VBA. I would also like to be able to create specific sheets within the workbook where each query can be exported into once the code is built. Please let me know if this is clear.
Thanks much!

Kal

 

[Isaac]

You can use Excel automation to start up Excel, and use excel's Range.CopyFromRecordset method to place the results of a query (which you'd already have open as an Access DAO recordset, also in code).

Look into things like createobject("excel.application"), workbooks.open method, declaring and setting an excel worksheet, declaring and setting an excel range, range.copyfromrecordset, declaring and setting a dao.recordset.

 

[Kmathieu]

You can use Excel automation to start up Excel, and use excel's Range.CopyFromRecordset method to place the results of a query (which you'd already have open as an Access DAO recordset, also in code).

Look into things like createobject("excel.application"), workbooks.open method, declaring and setting an excel worksheet, declaring and setting an excel range, range.copyfromrecordset, declaring and setting a dao.recordset.

Thank you! sounds like a good place to start.


The goal is to multiple sheets to the workbook. Can you please tell me if the below make sense?

Private Sub exportToXl_Click()

Dim Xlapp As Object
Dim Xlbook As Object
Dim Xlsheet As Object
Dim Xlrange As Object
Dim lngLastRow As Long
Dim sfilename As String


Set Xlapp = CreateObject("excel.Application")
Xlapp.Visible = True

Set Xlbook = Xlapp.Workbooks.Open(File path)

The goal is to multiple sheets to the workbook. is this how i should go about it? Please see the below
Xlbook.sheets(1).Name = "sheet 1"
Set Xlbook.sheets(2).Name = "sheet2"

 

[Isaac]

The goal is to multiple sheets to the workbook. is this how i should go about it? Please see the below

is to do what to them? maybe you missed a word

 

[Minty]

The simplest route would be to use DoCmd.TransferSpreadsheet from Access

Dim strSaveFile as String

strSaveFile = "c:\MyDocs\MyExcelFile.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName1", strSaveFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName2", strSaveFile
etc. etc.

This would store each query in a sheet with the query name as the sheet name, it would then be simple to change the sheet names if required

 

[theDBguy]

The simplest route would be to use DoCmd.TransferSpreadsheet from Access

Dim strSaveFile as String

strSaveFile = "c:\MyDocs\MyExcelFile.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName1", strSaveFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName2", strSaveFile
etc. etc.

This would store each query in a sheet with the query name as the sheet name, it would then be simple to change the sheet names if required

I agree. You can also preassign the sheet name, so renaming later won't be necessary.

 

[Kmathieu]

is to do what to them? maybe you missed a word

I meant send or export multiple queries to the workbook.

Thanks

 

[Gasman]

See if this helps. Someone else was trying to do the same thing.

At first, I thought it might have been you.

https://www.accessforums.net/showthread.php?t=82621&highlight=export+query

 

[Isaac]

I meant send or export multiple queries to the workbook.

Thanks

I guess I'd have to agree with others - if you want the quick 'n dirty way, use docmd.transferspreadsheet.

I've recently had problems with that (such as cutting off text at 255 char's), which made me tend to eschew it altogether in favor of Copyfromrecordset. Which takes a bit more work, but also has fewer issues IMHO.

 

[Minty]

I meant send or export multiple queries to the workbook.

Thanks

Did you not see the simplest method in post #7 ?

 

[Kmathieu]

Did you not see the simplest method in post #7 ?

I just saw it. I will give it a go. I will let you know how it turns out!

Thanks you!
Kal

 

[Kmathieu]

I guess I'd have to agree with others - if you want the quick 'n dirty way, use docmd.transferspreadsheet.

I've recently had problems with that (such as cutting off text at 255 char's), which made me tend to eschew it altogether in favor of Copyfromrecordset. Which takes a bit more work, but also has fewer issues IMHO.

Thank you Isaac, I will give it a shot.

 

[Kmathieu]

The simplest route would be to use DoCmd.TransferSpreadsheet from Access

Dim strSaveFile as String

strSaveFile = "c:\MyDocs\MyExcelFile.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName1", strSaveFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName2", strSaveFile
etc. etc.

This would store each query in a sheet with the query name as the sheet name, it would then be simple to change the sheet names if required

Hi Minty,
This did work, thanks! Now i am trying to format the sheet.