Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51

    Error 3027 - acSpreadsheetTypeExcel12Xml

    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Can you not export to .xls and run macros in that?

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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

  4. #4
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    Quote Originally Posted by ssanfu View Post
    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.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by ssanfu View Post
    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)
    Last edited by isladogs; 11-30-2018 at 05:09 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ 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. :-(

  7. #7
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    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?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Steve
    No problem. More importantly how are things in Alaska after your recent big quake.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @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).

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.......

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Wasn't the first one a 7?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes. Followed by a 5.7
    Paraphrasing Elvis: "We're All shook up!"

  14. #14
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    Quote Originally Posted by ssanfu View Post
    @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

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-15-2018, 02:52 PM
  2. Replies: 4
    Last Post: 02-01-2018, 01:30 AM
  3. error 3027 on backend file
    By vicsaccess in forum Access
    Replies: 3
    Last Post: 06-09-2016, 11:13 AM
  4. Error 3027 when importing text file
    By tym in forum Access
    Replies: 4
    Last Post: 12-12-2011, 12:53 PM
  5. Runtime Error 3027
    By jsbotts in forum Queries
    Replies: 4
    Last Post: 08-27-2011, 06:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums