Transfer spreadsheet issue

[Cretin]

I am trying to read a table in access2013 and export it to a spreadsheet in excel2013. I am actually naming the spreadsheet using a value in one of the tables fields. It works well except when I try to open the spreadsheet it gives me the error: "excel cannot open the file 'Weekly_timesheet_summary_20160730.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file format matches the format of the file". Belo0w is my code:

Dim strfilename As String
Dim db As Database

Set db = CurrentDb()
Dim strDate As DAO.Recordset
Dim LSQL As String
Dim LGST As String
Dim strpath As String


LSQL = "select Date from Time_Summary"

Set strDate = db.OpenRecordset(LSQL)
strpath = "U:\Projects\time tracker\testing\"
If strDate.EOF = False Then
LGST = strDate("Date")
Else
LGST = "Not Found"
End If

strfilename = strpath & "Weekly_timesheet_summary_" & Format(LGST, "yyyymmdd") & ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, "Time_Summary", strfilename, True
strDate.Close
Set strDate = Nothing
GetGST = LGST

In the line

DoCmd.TransferSpreadsheet acExport, 10, "Time_Summary", strfilename, True

I also used acspreadsheettype12 to no avail.

Cretin

 

[Cretin]

I did try to open and repair the spreadsheet to no avail

Cretin

 

[Andrzejek]

Did you try it with just [tt].xls[/tt] extension?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Cretin]

yes same thing but I have another idea.

Is what I am trying top do is generate a spreadsheet named with the date of the data such as weekly_report_20160730. I can get the data to a spreadsheet easily with a macro but had an issue using the macro to rename it so maybe instead of grabbing the data from the table I will still use the macro and just rename it using VBA. I will try a rename in the VBA module.

Cretin

 

[Cretin]

I think I have completed this successfully. My code is

 LSQL = "select Date from Time_Summary" 'SQL to retrieve date from the time summary table

'gets the date from the date field of the time summary table.

Set strDate = db.OpenRecordset(LSQL)
If strDate.EOF = False Then
LGST = strDate("Date")
Else
LGST = "Not Found"
End If

Name "U:\Projects\time tracker\testing\time_summary.xlsx" As "U:\Projects\time tracker\testing\weekly_project_summary_" & Format(LGST, "yyyymmdd") & ".xlsx"

Now comes the real challenge if it's even possible. We have a report. That report runs off of a query. In this query you need to enter a start date and an end date to pull records between a date span. I would like to name that report Report_startdate_enddate where start date is the beginning date of the report and end date is the ending date of that report, I know good luck hehe.

I am open to suggestions


Cretin

 

[Cretin]

I did get the date range to work click on the link below
Link

Cretin