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


    Quote Originally Posted by ssanfu View Post
    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.
    So if I'm understanding you correctly (and my own tests have shown the same i believe). You can only do a transferspreadsheet into an existing .xlsm file, not a new one.

    Changing this bit "FilToSave = FilName & ".xlsm", won't change that as far as i can see?

    Now that kinda gives me two questions:
    Firstly is why can't it create an .xlsm file when it can create an .xlsx (if I change FilName & ".xlsm" to "xlsx").
    Secondly if I do a transferspreadsheet into an existing .xlsm file, will it overwrite the existing sheets if they have the same names? (I hope that they will).

    Thanks for all your support and time Steve.

  2. #17
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by FoolzRailer View Post
    in black type
    So if I'm understanding you correctly (and my own tests have shown the same i believe). You can only do a transferspreadsheet into an existing .xlsm file, not a new one.
    That seems to be correct (from my testing).


    Changing this bit "FilToSave = FilName & ".xlsm", won't change that as far as i can see?
    Correct (but you need to ensure that you remove the extension from the value in "FilName" BEFORE you add the ".xlsm". Otherwise you end up with "D:\Forum\test.xlsx.xlsm")

    Now that kinda gives me two questions:
    Firstly is why can't it create an .xlsm file when it can create an .xlsx (if I change FilName & ".xlsm" to "xlsx").
    It might be because he code for "TransferSpreadsheet" was written back in the Access 95/97 days before Excel was so picky about the file types and the code wasn't modified for the new format/security.

    Secondly if I do a transferspreadsheet into an existing .xlsm file, will it overwrite the existing sheets if they have the same names? (I hope that they will).
    Don't know. You have the data/code.... did you try it? What was the result?

Page 2 of 2 FirstFirst 12
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