Export Access Table to Excel Temple using macro

[griffibr]

How would I generate a macro to export an access table or query to an existing excel template.

And, suppose I want to export two table to the same excel file, just different tabs. Can this be done using the macro option in access?

Thank you,

Bryant

 

[tuktuk]

here is how i do it:

create a macro and add run code and name the code you run in the lower half of the screen.

then add a module with that name using the docmd transferspreadsheet.


more specifically in the macro
setwarnings : no
runcode: function name: ExportTbltoExcel()
setwarnigns: yes

then in the module

Public Function ExportTbltoExcel()
Const FILE_PATH As String = "C:\my documents\"
Dim FullPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, , "YOUR QUERY NAME", strFullPath & "YOUR EXCEL FILE NAME.XLS", False
MsgBox ("Export complete")
End Function

if you have a worksheet title "YOUR QUERY NAME" the export will always overwrite the data within...SOMEONE PLS CONFIRM.

hope this helps..

i am curios to see the other responses...im case i'm taking the high road on this

 

[griffibr]

I'm getting close, thanks to you.
However, I'm getting error messages.
What am I doing wrong?


Public Function ExportTbltoExcel()
Const FILE_PATH As String = "C:\My Documents\Roles\MCP\Access\"
Dim strFullPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, , tblProducts, strFullPath & "test.XLS", False
MsgBox ("Export complete")
End Function

 

[griffibr]

"The expression you entered has a function name that Microsoft Office Access can’t find."

I obtained the above message for the following code:

Private Sub ExportTbltoExcel()
Dim strExcelFile As String
Dim strWorksheet As String
Dim strTable As String
strExcelFile = "C:\My Documents\Roles\MCP\Access\"
strWorksheet = "Worksheet1"
strTable = "tblProducts"
DoCmd.TransferSpreadsheet acExport, "MiceosftExcel(*.xls)", strTable, strExcelFile & "test.xls",False, strWorksheet"
MsgBox "Export Complete"
End Sub

Thanks for your help!

 

[xenou]

These are the arguments in TukTuk's post for the TransferSpreadsheet Method:
1. acExport
2.
3. query name
4. fullpath
5. False

These are your arguments for the TransferSpreadsheet Method:
1. acExport
2. "MiceosftExcel(*.xls)"
3. strTable
4. strExcelFile & "test.xls"
5. False
6. Worksheet

Besides the fact that "MiceosftExcel" cannot be a file filter, I'm not sure you are using the right argument for #2 - I believe it should be a constant, such as acSpreadsheetTypeExcel8 - or better left as the default as TukTuk has done. Read the help files on TransferSpreadsheet Method and see if you are using the right arguments, or try to follow TukTuk's example more closely. Your # 6 also looks suspicious, and is probably not needed anyway.

HTH

 

[griffibr]

Thank you.
I try Tuk's approach first. But, I obtain a different error message.
I will duplicate and send the error message.
Thanks.

 

[griffibr]

I initially took TukTuk's advice. I apparently did something wrong.
I use the following steps, gleaned from TukTuk's note:
create a macro and add run code and name the code you run in the lower half of the screen.

then add a module with that name using the docmd transferspreadsheet.


more specifically in the macro
setwarnings : no
runcode: function name: ExportTbltoExcel()
setwarnigns: yes

then in the module:
Option Compare Database
Public Function ExportTblToExcel()
Const FILE_PATH As String = "C:\My Documents\"
Dim FULLPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tblCars", strFullPath & "Test.xls", False
MsgBox ("Export Complete")
End Function

I obtained the following error message:
“The action or method requires a Table Name argument”

Thanks for your response.

Bryant<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[xenou]

Here is my code in a button. I always use the Call keyword:

Private Sub Command0_Click()
    Call ExportTblToExcel
End Sub

Here is the called routine. The problem may be simply that you've declared this as a function. It looks like it should be a subroutine.

Public Sub ExportTblToExcel()
Const FILE_PATH As String = "C:\My Documents\"
Dim FULLPath As String
    strFullPath = FILE_PATH
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tblCars", strFullPath & "Test.xls", False
    MsgBox ("Export Complete")
End Sub

It could work as a function but you'd need to give it a return value.

Does this work now?

EDIT: Note, Are you sure you have a table named tblCars? The code above successfully exported a table in my test database.

 

[griffibr]

Hello Alexander,

Thank you. I'll give it a try.
I'll let you know what happens.

Bryant

 

[griffibr]

Alexander,

This must be over my head.
I took the following steps:
Here is my code in a button. I always use the Call keyword:
Code:
Private Sub Command0_Click()
Call ExportTblToExcel
End Sub

Here is the called routine. The problem may be simply that you've declared this as a function. It looks like it should be a subroutine.
Code:
Public Sub ExportTblToExcel()
Const FILE_PATH As String = "C:\My Documents\"
Dim FULLPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tblCars", strFullPath & "Test.xls", False
MsgBox ("Export Complete")
End Sub

I obtained the following:

Run-time error '3044':
'C:\My Documents\Test.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Thanks again,

Bryant