VB Help Needed For Export Button

[Aamer]

I am using the following code to export Tables

Private Sub Export_Data_Click()
On Error GoTo Export_Data_Err

DoCmd.OutputTo acOutputTable, "Purchases", "ExcelWorkbook(*.xlsx)", "",
False, "", 0, acExportQualityPrint
DoCmd.OutputTo acOutputTable, "Sales", "ExcelWorkbook(*.xlsx)", "",
False, "", 0, acExportQualityPrint
DoCmd.OutputTo acOutputTable, "Company", "ExcelWorkbook(*.xlsx)", "",
False, "", 0, acExportQualityPrint
DoCmd.OutputTo acOutputTable, "Sub Description",
"ExcelWorkbook(*.xlsx)", "", False, "", 0, acExportQualityPrint


Export_Data_Exit:
Exit Sub

Export_Data_Err:
MsgBox Error$
Resume Export_Data_Exit

End Sub


This saves Files in Excel as:

Purchases
Sales
Company
Sub Description

Where as I want it to save as the following:


Purchases With Current Date example Purchase 07 Jan 2010
Sales With Current Date example Purchase 07 Jan 2010
Company With Current Date example Purchase 07 Jan 2010
Sub Description With Current Date example Purchase 07 Jan 2010


Can someone please help me fix the code.


Thanks

Aamer

 

[Douglas J. Steele]

Try

DoCmd.OutputTo acOutputTable, "Purchases " & _
Format(Date(), "dd mmm yyyy"), _
"ExcelWorkbook(*.xlsx)", "", _
False, "", 0, acExportQualityPrint

 

[Aamer]

Douglass

I entered the code as you described:

Private Sub Export_Data_Click()
On Error GoTo Export_Data_Err

DoCmd.OutputTo acOutputTable, "Purchases" & _
Format(Date, "dd mmm yyyy"), "ExcelWorkbook(*.xlsx)", _
"", False, _
"", 0, acExportQualityPrint

DoCmd.OutputTo acOutputTable, "Sales" & _
Format(Date, "dd mmm yyyy"), "ExcelWorkbook(*.xlsx)", _
"", False, _
"", 0, acExportQualityPrint

DoCmd.OutputTo acOutputTable, "Company" & _
Format(Date, "dd mmm yyyy"), "ExcelWorkbook(*.xlsx)", _
"", False, _
"", 0, acExportQualityPrint

DoCmd.OutputTo acOutputTable, "Sub Description" & _
Format(Date, "dd mmm yyyy"), "ExcelWorkbook(*.xlsx)", _
"", False, _
"", 0, acExportQualityPrint

Export_Data_Exit:
Exit Sub

Export_Data_Err:
MsgBox Error$
Resume Export_Data_Exit

End Sub


When I clik on the botton it asks me the location to save with file name as
Purchases07 Jan 2010 which is fine and exactly what I was looking for.

But the problem is when I "OK" it
I get the prompt "The Search Key Was Not Found In Any Record"

And the data is not saved.



Please Tell me what am I doing Wrong.

 

[Douglas J. Steele]

Have you tried using TransferSpreadsheet instead of OutputTo?

 

[Aamer]

What is transferspreadsheet
My knowledge is extreemly limited on VB.

 

[Douglas J. Steele]

http://msdn.microsoft.com/en-us/library/aa220766(office.11).aspx

 

[Aamer]

Isn't there anything simple that can do the job, As the stuff you told me
went flying over my head.

can it be done by a macro?