Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    transferspreadsheet (2003)

    When I attempt to import transfer a "Read-Only" spreadsheet, I receive the error message "The file abc.xls doesn't contain any data"
    If I open the spreadsheet and "save-as" another name, the transfer spreadheet works fine.
    Is there some way around this limitation to allow 'read-only' transfers?
    Is there a way to macro/vba call the read-only file and "save as" in an automated 'click the button' process?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transferspreadsheet (2003)

    In what sense is the spreadsheet read-only? I can import an Excel spreadsheet without problems using TransferSpreadsheet, even if the .xls file is read-only, or if the workbook has been saved with "read-only recommended".

  3. #3
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transferspreadsheet (2003)

    The folder that the spreadsheet resides in is the read-only culprit. I'm forced to save-as to my desktop and transfer from there.
    The goal here is to create the transferspreadsheet link in the macro that will work from multiple systems within the workgroup.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transferspreadsheet (2003)

    I can't reproduce this - it doesn't matter whether a folder or workbook is read-only, I can import or link without errors. Are you sure there isn't another problem with the workbook?

  5. #5
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transferspreadsheet (2003)

    This folder is locked out by our IT dept. It is used as a repository for autogenerated spreadsheets. I cannot write a file to it.
    I don't know what other problem there might be with the workbook. As noted previously, everything works fine with a 'save-as' and transfer.
    Is there an automated way to open the spreadsheet and then 'send-keys' to save-as and perform the transfer on the new name?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transferspreadsheet (2003)

    You don't need to open the spreadsheet, you can copy it to another folder. For example:

    Sub CopyAndImport()
    Dim strOriginalPath As String
    Dim strDesktop As String
    Dim strFile As String

    ' Modify as needed, but keep trailing backslash
    strOriginalPath = "serversharefolder"
    ' User's desktop folder
    strDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk top") & ""
    ' Modify as needed
    strFile = "MySpreadsheet.xls"
    ' Copy file to desktop
    FileCopy strOriginalPath & strFile, strDesktop & strFile
    ' Import into database; modify table name as needed
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "tblXL", strDesktop & strFile,True
    End Sub

  7. #7
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transferspreadsheet (2003)

    The modified vb code copies the file to my desktop. However, the same error message comes up when the transfer is attempted, "Cannot transfer an empty spreadsheet". Opening file on desktop shows the data.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: transferspreadsheet (2003)

    is the workbook the first workbook in the xls sheet?

    has the copy been done to the other folder?

    does the program try to open the spreadsheet in the target folder?

  9. #9
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transferspreadsheet (2003)

    1. It is the only workbook in the sheet
    2. Don't understand this question.
    3. Yes

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: transferspreadsheet (2003)

    q2. has the FileCopy command worked?. has the database been copied to the target folder?

  11. #11
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transferspreadsheet (2003)

    The filecopy works and the spreadsheet copies to the target folder. However, trying to transferspreadsheet gives same "Cannot transfer empty folder" message. If I manually open the target spreadsheet and then do a "save as" to same target name/folder, I can then perform the transfer without problem. Just a copy does not allow transfer.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: transferspreadsheet (2003)

    Would you give us a copy of the spreadsheet and the database so i can try it here?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transferspreadsheet (2003)

    I have attached a text file with code to open the workbook in Excel and save it in another folder, then import it into Access.
    You will need to modify the path and file names.

  14. #14
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transferspreadsheet (2003)

    The code attachment performs the function I needed and works well. If I want to change the target to another location, is it just a matter of changing ("desktop") to ("c:cccyyy") or does .specialfolders define just that ?
    Example: strTargetPath = CreateObject("WScript.Shell").SpecialFolders("c:ab cdef") & ""
    Thanks for al the help!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transferspreadsheet (2003)

    If you want to use a fixed location, simply use something like

    strTargetPath = "C:abcdef"

    I only used CreateObject("WScript.Shell").SpecialFolders("Desk top") because you mentioned that you wanted to save the spreadsheet to the desktop, and that is not a fixed location - it varies from user to user.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •