Results 1 to 4 of 4
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    Import select columns from Excel

    I have an Excel file that I need to import into Access. the file is a set format that i am not allowed to edit (or ask to have edited) and contains multiple sheets. I need to pull data from one specific sheet and the data starts on row 6 (header row) and can contain anything from 20 rows to a couple thousand. The Excel file has 119 columns for each data row and not all the headers can be field names in Access. I am in a battle with the project owner to only pull in the 15 columns that are needed in the database and am proceeding down that path..... What I want to do is pull in specific columns and all the rows in sheet (example column A, C, S, AA,.... ) starting at row 7 through however many rows are in the file from the specific sheet. Using the headers in the Excel file may be a problem because there are special characters in many of them. the other "challenge" is not all the fields will be populated all the time (blanks in the data)



    In a prefect world, i would have someone move the needed data to an Excel upload template then just pull in the template but the project owner thinks that would be too challenging for the user doing it. Of course the project owner also wants all 119 fields in the table.

    any help, guidance, or code examples would be greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why does the header start on row6? why not 1?
    is this a true excel file or csv?

    I use code so the user can select an excel file (with non row1 headers)
    then the code removes the rows so headers are in col 1.
    then saves the xl file to the same place: c:\temp\File2Import.xls
    (which is a linked external table) so then it runs a query to import ONLY the columns I want.

  3. #3
    Sck is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2010
    Posts
    82
    it is a working file with it's own VBA module (I dont have pw for it) the data i need starts on row 7 (header on row 6). the data in rows 1-5 is summary information and multiple buttons to run various processes in the Excel file. the excel file exports various reports and uploads that are used in other corporate level systems. I am not allowed to make any changes to the file. I am not sure if any vba processes run when the file is opened but i know there is one that runs and requires user input when it closes.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    perhaps something from here will get you started

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Import data from Excel into Table columns only
    By Kath81 in forum Import/Export Data
    Replies: 1
    Last Post: 06-29-2021, 01:54 PM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. VBA Import excel >255 columns with Transferspreadsheet
    By SteveApa in forum Import/Export Data
    Replies: 1
    Last Post: 06-23-2016, 11:08 AM
  4. Import Excel Worksheets and Skip Certain Columns
    By vcs1161 in forum Import/Export Data
    Replies: 5
    Last Post: 07-20-2015, 04:36 PM
  5. Import from excel when data is in different columns - beginner
    By faodavid in forum Import/Export Data
    Replies: 5
    Last Post: 08-03-2014, 12:04 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