Error 3027 - acSpreadsheetTypeExcel12Xml

**FoolzRailer** · 11-29-2018, 07:23 AM

Hello

I have the two following code parts (the first gives me a FileDialog box for saving, the second does the transferspreadsheet part). Before I tried exporting using acSpreadsheetTypeExcel12 and xlsx, it worked fine. But when I try using acSpreadsheetTypeExcel12XML and save it to xlsm, it gives me an error 3027. Is it not possible using the below parts, to export to an xlsm macro-enabled workbook?

Code:

Option Compare Database
Public Function FilToSave()

Dim FlDia As FileDialog

Set FlDia = Application.FileDialog(msoFileDialogSaveAs)

With FlDia
    .AllowMultiSelect = False
    .InitialFileName = "C:\"  ' You can set outfile to a full path with a fictitious  or real file name, and the dialog will open in that folder.
    .Title = "Navngiv filen med det ønskede navn"
    If .Show = True Then
        FilName = .SelectedItems(1)
       
    Else
        MsgBox "No file selected. Process cancelled"
        DoCmd.Hourglass False
        FilToSave = "Cancelled"
        Exit Function
        End If
End With
 
FilToSave = FilName & ".xlsm"

End Function

Code:

Public Function Export2Queries()
Dim savefile As String
savefile = FilToSave
If savefile <> "Cancelled" Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Price", savefile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Unik", savefile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Fourbeholdning", savefile, True
End If
End Function

**ranman256** · 11-29-2018, 08:14 AM

Can you not export to .xls and run macros in that?

**ssanfu** · 11-29-2018, 07:19 PM

My first thought would be "Are you writing the .xlsm to a trusted location?".

Then I saw

Code:

Set FlDia = Application.FileDialog(msoFileDialogSaveAs)

Did you know:
There are 4 options you can choose from as a Dialog but the msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access.
See https://599cd.com/tips/access/150405_filedialog/
In my code, I use "msoFileDialogFilePicker" and "msoFileDialogFolderPicker".
I mostly use the folder picker (msoFileDialogFolderPicker) with the file name created/set in the code.

Why do you have 2 routines? I have 1 routine - the button click - to pick/get the folder name and then export the data using "OutputTo" or automation.

BTW, the top two lines in EVERY module should be

Code:

Option Compare Database
Option Explicit

**FoolzRailer** · 11-30-2018, 05:59 AM

Originally Posted by ssanfu

My first thought would be "Are you writing the .xlsm to a trusted location?".

Then I saw

Code:

Set FlDia = Application.FileDialog(msoFileDialogSaveAs)

Did you know:
There are 4 options you can choose from as a Dialog but the msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access.
See https://599cd.com/tips/access/150405_filedialog/
In my code, I use "msoFileDialogFilePicker" and "msoFileDialogFolderPicker".
I mostly use the folder picker (msoFileDialogFolderPicker) with the file name created/set in the code.

Why do you have 2 routines? I have 1 routine - the button click - to pick/get the folder name and then export the data using "OutputTo" or automation.

BTW, the top two lines in EVERY module should be

Code:

Option Compare Database
Option Explicit

Thanks for the reply, I will look into what you are saying.

Now just curious as I'm a beginner at programming and currently running my Modules from Macros (Which is just fine as to what i need). The msoFileDialogSaveAs does give me a dialog option when I run the code with xlsx and doesn't give a error 3027. If it isn't supported how is it, that it works?

The reason for two routines, is basically just that I'm new to this, and haven't been optimizing anything, so just going by how I learn.
I'm not having issues with the exporting if I use the .xlsx file-extension, which is why I don't believe there is an issue with the trusted location part, but I've added my export folder to trusted locations.

I have enabled the Microsoft Office 16 Object Library.

**isladogs** · 11-30-2018, 06:48 AM

Originally Posted by ssanfu

Did you know:
There are 4 options you can choose from as a Dialog but the msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access.

Sorry Steve but this is completely untrue.
All 4 msoFileDialog options INCLUDING Open & SaveAs work in Access.
I tried to view the link but its down at the moment

I regularly use SaveAs & have occasionally used Open

For example:

Code:

 ...
 ' Set options for the dialog box.        Dim F As FileDialog
        Set F = Application.FileDialog(msoFileDialogSaveAs)
        F.Title = "Save As  . . ."
        
    ' Clear out the current filters
      '  F.Filters.Clear
        
    ' Set the start folder
       ' f.InitialFileName = "c:\"
        
    ' Call the Open dialog routine.
        F.Show
    
    ' Return the path and file name.
        stFilename = F.SelectedItems(1)

In fact I swopped over to msoFiledialog a few years ago as the code I used previously doesn't work in 64-bit Access
For example, this won't work in 64-bit:

Code:

  Dim msaof As MSA_OPENFILENAME    Dim FileNamelength As Integer
    
    ' Set options for the dialog box.
    msaof.strDialogTitle = "Locate the file and click on 'Open'"
    msaof.strInitialDir = strSearchPath
    msaof.strFilter = MSA_CreateFilterString("All files", "*.*")
    
    ' Call the Open dialog routine.
    MSA_GetOpenFileName msaof
    
    ' Return the path and file name.
    FindFilePath = Trim(msaof.strFullPathReturned)

**ssanfu** · 12-03-2018, 09:21 PM

@ Colin

Thanks for the info.
I saw several sited that said msoFileDialogOpen and msoFileDialogSaveAs were not supported.
I do use msoFileDialogFilePicker and msoFileDialogFolderPicker, but never used or tested the other two. :-(

**FoolzRailer** · 12-04-2018, 12:57 AM

Now that is out of the way (I read the same sites as you Steve, but found that it worked despite that

), any suggestions on how to get passed my error 3027 Database or object is read-only?

**isladogs** · 12-04-2018, 03:21 AM

Hi Steve
No problem. More importantly how are things in Alaska after your recent big quake.

**ssanfu** · 12-04-2018, 06:29 PM

@FoolzRailer

Any chance of you posting your dB so we can test what you have? Change any sensitive data - only need 10 - 20 records........

@ Colin

Things were shaky for a few hours, but has since calmed down.

(pun intended

)

I survived, house survived with no damage, lost 1 picture and lots of stuff on the floor. (time to get busy and throw a lot of "stuff" away).

**isladogs** · 12-04-2018, 06:35 PM

Glad you came out of it relatively unscathed. Hadn't realised how frequently Alaska is subject to quakes.
Don't think I've ever experienced anything above 3.0! One blessing of being UK based!

**ssanfu** · 12-04-2018, 06:42 PM

Usually you can hear the 4.5 - 5.5 quakes coming.
Sounds kind of like a jet plane in the distance. Then the shaking begins. Hear it once, you never forget the sound...
And 30 seconds of shaking feels like an hour! Especially the 6.0+ quakes.

Edit: Just had another shake. Office on 2nd floor - felt about 3.5.......

**isladogs** · 12-04-2018, 07:24 PM

Wasn't the first one a 7?

**ssanfu** · 12-04-2018, 08:09 PM

Yes. Followed by a 5.7
Paraphrasing Elvis: "We're All shook up!"

**FoolzRailer** · 12-07-2018, 04:13 AM

Originally Posted by ssanfu

@FoolzRailer
Any chance of you posting your dB so we can test what you have? Change any sensitive data - only need 10 - 20 records........

Sorry, I didn't get an update with new replies mail, so didn't see your responses.

I've scrubbed the database and just added some testlines in the tables instead, so have at it

Hope all is well in regards to the earthquake!

Prissammenligning1.mdb

**ssanfu** · 12-07-2018, 08:47 PM

In the function "Export2Queries()", there is a line "savefile = FilToSave".

If I select "D:\Forum\test.xlsx", the function "FilToSave"returns "D:\Forum\test.xlsx.xlsm" (because of this line: "FilToSave = FilName & ".xlsm").

Back in the function "Export2Queries(), the first DoCmd.TransferSpreadsheet statement tries to execute and an error occurs because there is no such file.

Further testing:
if the extension is ".xlsx", executing the statement "DoCmd.TransferSpreadsheet" will create the Excel file and export the data to the file.

if the extension is ".xlsm", executing the statement "DoCmd.TransferSpreadsheet" will FAIL if the file does not currently exist.
if the extension is ".xlsm", executing the statement "DoCmd.TransferSpreadsheet" will SUCCEED if the file exists BEFORE the function "Export2Queries()" is executed.

Error 3027 - acSpreadsheetTypeExcel12Xml

Thread Tools

Error 3027 - acSpreadsheetTypeExcel12Xml

Similar Threads

Cannot update. Database or object is read-only. (Error 3027)

Access 2010 - transferspreadsheet generates (sometimes) error 3027

error 3027 on backend file

Error 3027 when importing text file

Runtime Error 3027

Tags for this Thread

Posting Permissions