Why won&#x27;t acSpreadsheetTypeExcel12Xml create the .xlsx file

It looks like you copy a workbook to the file that you export the table data to just before the export. Are you sure that is a "good" XLSX file to start with?

»bp

First, make sure your CopyFile() line of code works:

 FileCopy pathf & fname & ".xlsx", paths & fnameo

Open in new window

You should have a file in the target folder with the target file name after that line executes. If you don't, then you've got nothing to export to.
Also, make sure to check for existing files with the folder and name that you are about to create. You'll want to delete (kill) anything that you are about to overwrite.

ASKER

Thanks it wasn't correct. I fixed it but now I get an error. It creates the file but it's empty and when it opens from the code I just see a grayed out spreadsheet, no cells show up. I'm told the file format or extension is not valid. If I open it from the folder it resides in it's blank but the cells show up.

Also make sure that the path of the file you are trying to open is the same as the one you just created. It looks like it should be, but you never know. Be sure and check. Best to create the entire full path file string than having to recreate it every time.

Your transferspreadsheet function is targeting a named range. Does that named range exists? Also, is the workbook/sheet protected?

So you are running your Access app in Access 2016? What type of file is your Access app?

Just a note: you can't change a .xls file to a .xlsx file by just changing the extension. You MUST open the file in a newer version of Excel and SAVE IT AS a .xlsx. Otherwise, you'll get screwey stuff like you are experiencing..

ASKER

No, the workbook/sheet is not protected and there is a named range that does exist.

ASKER

I'm not changing it. I'm using an .xlsm file to create an .xlsx file. It worked for two years using 2010 apps but the end users were updated to 2016. It still worked with the 2016 apps even though the newly created Excel file prior to the change was an .xls file but one of the users would get the file format or extension is not valid message. Once she clicked the "Yes" button the file would open but I was asked to fix it so the message didn't appear.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ok. so you are "copying" (not converting or saving-as) a .xlsm file to a new file with the extension ".xlsx"? I think that is where your issue lies. You can "copy" files with the same extension, but you'll need to "save-as" if they are different.

"Save-As" from .xlsm to .xlsx should strip-out the macros for you (with a possible warning).

ASKER

Thanks everyone. I couldn't get rid of the macro in Excel but did change the extension on the new file to an .xlsm and it worked!!

Looking at your original code too, I think you could simplify the hard coded paths just slightly to this:

Option Compare Database

Private Sub cmdExport_Click()

Dim pathf As String, fname As String, fnameo As String, paths As String

paths = "\\corpfs03\SHARED\hcf&um\SHARED\Weight Watchers Database\Weight Watchers - Reimbursements\Check Templates for AP\Current Year\"
pathf = paths & "Macro Templates\"
fname = "Template_Man_chk"

'file names with underlines
fnameo = fname & Format(Date, "mmddyyyy") & ".xlsx"
If Len(Dir(pathf & fname & ".xlsx")) > 0 Then
    FileCopy pathf & fname & ".xlsx", paths & fnameo
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryChecksSentToAP", paths & fnameo, True, "APChecks"
Else
    MsgBox "Original file is missing", vbOKOnly
End If

'WillR - opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
    Set xlWB = .Workbooks.Open(paths & fnameo, , False)
    End With
    
End Sub

Open in new window

»bp

Great news! ...now go forward and sin no more..... ;-)

Glad that helped.

»bp

ASKER

Thanks Bill, that will help. It makes it so much easier to read. I always learn better ways of doing things from this site.

Great, glad that was helpful! Thanks for the feedback.

»bp