Results 1 to 6 of 6
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Create Table With Field Names From Current Table

    I am using DoCmd.TransferSpreadsheet to import a spreadsheet to one table, delete some garbage data that always exists at the top of the spreadsheet, then I have good data, with the header info in row 1, and the data in row 2.

    How can I then append/update this data to a new table with the field names being what the 1st row is?

    I do not want to append to a pre-made table bc the field names can change from import to import. But what remains static is the fact that there is 2 rows of garbage at the top of the spreadsheet, then the header row is in row3, then data begins in row 4.



    I also know that with the DoCmd.TransferSpreadsheet you can specify a range such as "A3:G12" however, again the amount of data varies so I do not want to hardcode a range when the data could exceede the specified range.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use this method., first remove the unwanted rows until the field headers are in row 1.
    save this workbook to a generic filename everytime, i.e: "c:\folder\file2import.xls"
    link the workbook as an external table.
    then everytime you get a new file, overwrite the old one, and run the code below,
    it will open the excel file, remove the unwanted rows, and save, then close excel, and import the table.

    steps:
    1. overwrite old generic file,
    2. click Import button
    3. done.


    'you MUST put Excel in the program REFERENCES, in VBE menu (Ctl-G): TOOLS , REFERENCES
    'check the 'Microsoft Excel X.xx Object library'

    Code:
    sub btnImport_click()
    
    vFile =  "c:\folder\file2import.xls"
    
    FixSheet vFile                  'fix the data first
    DoCmd.openquery "qaImportData"          'NOW import the data
    end sub
    
    
    '============
    Sub CallXLsheet(pvFile)
    '============
    Dim xl As Excel.Application
    
    Set xl = CreateObject("excel.application")
    With xl
        .Workbooks.Open pvFile
        .Rows("1:3").delete
        .Range("A1").Select
        
        .ActiveWorkbook.Save
        .Quit
    End With
    Set xl = Nothing
    End Sub

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Works exactly as needed. Thank you for that, I never can get Access to Manipulate Excel - I always get odd errors.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You could also import range "A3:Z999".

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by aytee111 View Post
    You could also import range "A3:Z999".
    Would this ignore blank rows/columns?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It did for me when I tested it.

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

Similar Threads

  1. pulling field names from table
    By murfeezlaw in forum Queries
    Replies: 6
    Last Post: 11-27-2012, 04:30 PM
  2. Filtering on Field Names from Table
    By reddog1898 in forum Access
    Replies: 3
    Last Post: 05-13-2011, 10:47 AM
  3. Get Field names from a table in CurrentDB()
    By e.badin in forum Programming
    Replies: 8
    Last Post: 01-06-2011, 02:17 AM
  4. How to retrieve field names in a table
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-05-2010, 09:09 PM
  5. Replies: 5
    Last Post: 03-31-2009, 09:16 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