Results 1 to 3 of 3
  1. #1
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Central Illinois
    Posts
    185

    Link to Excel - Headers are dynamic


    Good morning.
    I am linking to an excel files, which a range of headers are dates. These dates change every week. I have not been able to find a solution which works, which would allow me to "refresh" the headers names automatically.
    I would also need the underlying queries, forms, and reports to be dynamic as well, which would be used against the linked table.
    Any thoughts on how to process this would be appreciated. This excel file feeds other excel reports, so I need to be able to run in both excel and access until I incorporate all the excel tools we use into the MS Access database. Otherwise, I would just do away with the excel files at once and import it all into Access.

    I should add, the data which feeds the Excel file, comes from our MRP System and a data extract which is pulled every week and automatically updates the excel file.

    Thank you in advance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    No coding really needed.

    '---prep
    Save the excel file to a generic name, say c:\temp\ImportFile.xls
    Attach (link) this file into access as a linked table, say: xlFile2Import (done just this once)
    Build a query to append the data from xlFile2Import to your target table, say: qaImportXLdata
    Build a macro to run the monthly import. mImportXL
    The macro will have the query : qaImportXLdata

    'functionality -----
    So every month,
    1. save your data to the generic file: ImportFile.xls (overwriting the previous one)
    2. Then run the import macro, mImportXL
    Done.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you would have to upload a small db sample with your existing db (the Access file) and an Excel sample with some "dummy" data.

    To make the process work you will need to somehow normalize your existing structure; the "variable" headers (which I read like they come in as column names when you link the Excel file) need to become another data column in an Access table (like ReportingDate or similar). To do that you could try to link the Excel file with the "First row contains field names" checkbox left unchecked; that would create a new record with the "header" names. You want to process that one first by running append queries to move it into a permanent Access table, one for each date value (so you append FIELD20 which holds 2/8/2022 to add into ReportingDate, then FIELD21 with 2/9/2022 into the sameReportingDate, etc.). Once you do that you add the rest of the data with another set of append queries filtered by the ReportingDate.

    Recreating the original format should be easy by using a crosstab query to get you the date headers back into the column position.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Dynamic link between 2 forms
    By geocan2006 in forum Forms
    Replies: 2
    Last Post: 02-27-2020, 11:54 AM
  2. Exporting to Excel - Column Headers
    By Xarkath in forum Access
    Replies: 2
    Last Post: 01-10-2014, 01:04 PM
  3. Export Report with Page Headers to Excel
    By EddieN1 in forum Reports
    Replies: 1
    Last Post: 09-01-2012, 12:37 PM
  4. Import Excel Headers to MS SQL
    By jshockency in forum SQL Server
    Replies: 1
    Last Post: 08-12-2011, 06:37 AM
  5. Dynamic Column Headers but NOT CROSSTAB
    By jtkjames in forum Queries
    Replies: 1
    Last Post: 07-26-2010, 05:16 AM

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