INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Transfer spreadsheet issue

Transfer spreadsheet issue

(OP)
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:

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

CODE -->

DoCmd.TransferSpreadsheet acExport, 10, "Time_Summary", strfilename, True 
I also used acspreadsheettype12 to no avail.

Cretin

RE: Transfer spreadsheet issue

(OP)
I did try to open and repair the spreadsheet to no avail

Cretin

RE: Transfer spreadsheet issue

Did you try it with just .xls extension?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Transfer spreadsheet issue

(OP)
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

RE: Transfer spreadsheet issue

(OP)
I think I have completed this successfully. My code is

CODE

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

RE: Transfer spreadsheet issue

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

Cretin

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close