Dynamic Export Table Name

[UnysonElliot]

Hi All,

I'm trying to export a table with a date attached to the end of it. I've seen a ton of examples on Google already but I can't get any of them to work for me. Currently I have tried it through the macro tool and in a module and no success.

Here's what I have in the module:

For the module I have the code and a Macro setup to RunCode calling Elliot()

Public Function Elliot()
Dim Current_Date As String
Dim File_Name As String
Current_Date = Format(Now(), “yyyymmdd hhmmAMPM”)
File_Name = “T:\GLOBAL INDUSTRIAL\Global Invoice Report\Global Invoice Shipment Report.xlsx-” + Current_Date + “.xls”
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, “Global Invoice Shipment Report“, [File_Name]
End Function

When I run the Macro I get an error that says Compile error: Syntax Error. When I click okay the first line highlights in yellow.

When I try the Macro Builder using ExportWithFormatting

Object Type: Query
Object Name: Global Invoice Shipment Report
Output Format: Excel Workbook (*.xlsx)
Output File: T:\GLOBAL INDUSTRIAL\Global Invoice Report\Global Invoice Shipment Report&Format(Date(),"yymmdd")&.xlsx
Auto Start: No
Template File:
Encoding:
Output Quality: Print

This does not work either.

Any help would be greatly appreciated.

 

[xenou]

If you're using vba, you would want the file name to end in xlsx. You seem to be giving two file extensions (xlsx and xls) at the same time to the same file, which can't be right.

Also, vba doesn't use "+" for string concatenation. It uses "&"

Not sure what you're googling but make sure the sites are relevant for MSAccess VBA

 

[UnysonElliot]

If you're using vba, you would want the file name to end in xlsx. You seem to be giving two file extensions (xlsx and xls) at the same time to the same file, which can't be right.

Also, vba doesn't use "+" for string concatenation. It uses "&"

Not sure what you're googling but make sure the sites are relevant for MSAccess VBA

Thanks for your help. I have made the changes but still getting errors in VBA.

This is my code:

Public Function Elliot()
Dim Current_Date As String
Dim File_Name As String
Current_Date = Format(Now(), “yyyymmdd hhmmssAMPM”)
File_Name = “T:\GLOBAL INDUSTRIAL\Global Invoice Report\Global Invoice Shipment Report” & Current_Date & “.xlsx”
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, “Global Invoice Shipment Report“, [File_Name]
End Function

When I run the macro that is RunCode, Function Name: Elliot(), it opens up the module and when I click in it I get the error of Compile Error: Syntax Error". When I click Okay it highlights the first line of the code in yellow and puts an arrow next to it. Not sure what my problem is here.

 

[xenou]

What do you mean by first line of code?
I'm not sure if you mean the line starting Current_Date = ... or the line starting DoCmd ...

Offhand, it looks like you put brackets around File_Name which doesn't work if you mean to use the variable File_Name

 

[UnysonElliot]

What do you mean by first line of code?
I'm not sure if you mean the line starting Current_Date = ... or the line starting DoCmd ...

Offhand, it looks like you put brackets around File_Name which doesn't work if you mean to use the variable File_Name

I took the brackets off and still getting the same error. It highlights the first line Public Function Elliot () in yellow. You can see it in the image that I attached.

 

[xenou]

make it a sub:

Public Sub Elliot()

Also by the way I always leave the excel type empty:

DoCmd.TransferSpreadsheet acExport, , “Global Invoice Shipment Report“, File_Name

Just on the reasoning that I have no idea what Excel9 is and it works using the default most of the time.

 

[Joe4]

Also, be aware of the double-quotes.
I have seen issues where people copy code from the internet, and Access wants the completely vertical double-quotes (") and not the slanted ones (“).

 

[UnysonElliot]

Also, be aware of the double-quotes.
I have seen issues where people copy code from the internet, and Access wants the completely vertical double-quotes (") and not the slanted ones (“).

Joe, I copied the quotes from an online code and Access didn't like the quotes. Replaced them with the correct quotes and it works! Thanks for your help.

 

[Joe4]

[FONT=&quot]Joe, I copied the quotes from an online code and Access didn't like the quotes. Replaced them with the correct quotes and it works! Thanks for your help.[/FONT]

You are welcome!

Sometimes its those little annoying things which will drive you crazy like that!