Results 1 to 2 of 2

Thread: Values Not Transfering (DoCmd.TransferSpreadsheet)

  1. #1

    Exclamation Values Not Transfering (DoCmd.TransferSpreadsheet)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have created individual "productivity reports" in excel that will be use by each member of my department. At the end of the day, I want them to be submitted to the access database I have set up.

    The "productivity report" sheet is done up with a large header that contains the company logo and branding, and there are also several hidden columns contain useful information that I need for reporting, but is unnecessary for the end user to see.

    To simplify the transfer process, I created a "productivity log" sheet that has no hidden columns and the column headers in the first row.

    Each cell on the "productivity log" is set up to equal the corresponding cell on the "productivity report". Such as: f(x)='Productivity Report'!B6

    I set up a module and entered my code. For the most part, everything transfers, but the values are missing for some columns.

    The data in the first three columns transfers just fine, these columns contain constantly filled out information and are hidden on the “productivity report”. It is the information that I fill out on the report, to test it out, that does not transfer. So I thought it might be a calculation issue, so I threw a code in to calculate before transferring. It worked ONCE. After it worked, I thought “great!” let me not try to format some stuff, and not sure how, but now its back to showing “0” in Access.

    I thought maybe the formatting in excel is throwing it off so I set everything back to general… I made most of the columns in access text fields, all but the ones that I knew worked before during my one sucessful test. Not sure what to do anymore.

    Code:
    Sub Submit()
    
    Application.CalculateFull
    
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase "C:\Users\J050524\Desktop\Department Reporting\Database\Database.accdb"
    objAccess.DoCmd.TransferSpreadsheet acImport, 10, _
    "Productivity_Log", "C:\Users\J050524\Desktop\Department Reporting\Productivity Reports\Productivity Report (E210026).xlsm", True, "Productivity Log!A1:N41"
        
    ' Clear Data from Productivity Report
        Range("G6:G45,I6:I45,O6:O45").Select
        Selection.ClearContents
        Range("G6").Select
        
    End Sub

  2. #2
    I was able to answer my own question. When I identify what in the workbook I want to transfer to access, I have the file path: "C:\Users\J050524\Desktop\Department Reporting\Productivity Reports\Productivity Report (E210026).xlsm"

    However, the workbook remains open all day on the colleagues desktop and is not saved at any point because they are constantly filling it out. So when it looks at the file path, the workbook is not populated with any values because it has not been saved.

    I changed the code to read: "ActiveWorkbook.FullName" and also threw in a "ActiveWorkbook.Save" before and after the transfer. This did the trick.

Posting Permissions

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