Results 1 to 4 of 4
  1. #1
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15

    Import Excel 2010 worksheet when xlsx first row may or may not contain Access field names

    Am importing Excel 2010 xlsx that are reports generated by QuickBooks Enterprise 2015 Online and output as xlsx. files.

    Problem is that sometimes QuickBooks includes the Report Heading in the xlsx and sometimes it does not. Per QB, it is not on their to fix list.

    Users are not Excel savvy, so this needs to be done without user involvement in Access.

    When the first row of the xlsx contains the Access table field names everything works great.


    Problem is when there is a heading, 1st row does not contain field names and Access names the columns F1 to F48 plus last column has a variable date/rime in first row so Access assigns that as the field name instead of [f49]. And as a result, all the subsequent queries do not work. (I thought of doing a query and simply renaming the filed names to correct names but since the last field has a variable time/date and is different every time the xlsx is imported, I could not figure out how to reference that column.

    What is best way to handle this?


    Attached pdf is of both sheets: With heading and Without Heading.

    Following is code I use to import the Excel 2010 xlsx. (Each QB xlsx always has 2 sheets and I always import only Sheet1 of each xlsx. )


    Private Sub Command0_Click()
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "QB ITEM LIST"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QB ITEM LIST", "C:\QB Reports as XLSX\QB ITEM LISTING.xlsx", True, "Sheet1!"




    DoCmd.SetWarnings True
    MsgBox "THIS PROCESS IS FINISHED, YOU CAN RETURN TO MAIN MENU!"
    DoCmd.OpenForm ("Switchboard")

    End Sub
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I had this problem too. Use an object to take over excel. Set XL = excel.application
    (Google this, I don't have the code here)
    the user would select a file, I would use the object above, open the XL book.
    then check cell a1 if it had the field name.
    If not, It would insert the headers, then save, then import.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    rather than using transferspreadsheet, consider linking to the excel file then using an append query to the destination table. Ensure you link assuming there are no headers then with the append query refer to a column which is number and use criteria something like

    Code:
    INSERT INTO tblDestination (destfld1, destfld2...)
    SELECT lnkSource.F1,lnkSource.F2,lnkSource.F3...
    FROM lnkSource
    WHERE isnumeric(numericfldname)
    The code is abbreviated - in reality, the fields in your source may be treated by access as text, numeric or date so you may need some additional functions to ensure data is treated correctly - you'll only find this out by inspecting the result and adjusting as required.

  4. #4
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    Solved the problem by avoiding it... QuickBooks has option to "hide header" on a report which assures the Excel file has column names in first row. QB Enterprise Online 2015 has ability to "memorize" a customized report... so that only correct layout for each specific report can be saved as xlsx.

    Would love to use ODBC to import QB data files, but since we are on a shared server, company hosting our QB Online blocks that. So, we have to go with what QB Online offers (and what we can afford).

    Thanks for the replies.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-28-2014, 11:17 AM
  2. Replies: 7
    Last Post: 09-24-2013, 04:53 AM
  3. Replies: 3
    Last Post: 08-22-2012, 06:28 AM
  4. VBA to Import Excel Worksheet
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 10:53 AM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 PM

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