Results 1 to 8 of 8
  1. #1
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143

    Best method to read specific lines of data from Excel into Access tables

    I would like to read specific cells from Excel spread sheet..
    B2, B3, B4, B7, B8 and then read all the lines from A12 to F16, A19 to D23, A28 to F35.

    Was able to read B2 to B8 as follows..
    MyRec.Fields("Unit_ID")=xlsx.cells(2, "B")
    But got truck reading lines from A12 to F16 and soon..

    Any thoughts?

    Thank you
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you look at the TransferSpreadsheet option to import into Access from Excel and specify the ranges?
    Or create a recordset from sql as is done here http://www.accessmvp.com/KDSnell/EXC...ort.htm#ImpSQL
    Or use automation (plenty of examples at that site).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Thank you referring to the example code..

    I am trying to use the code below but getting Compile error: Expected: Case near T1. Any idea?

    Also what should I replace Excel8.0 if using O365?

    SELECT T1.*, 1 AS SheetSource
    FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFolder\MyFile.xls].[WorksheetName$A2:U65536] as T1;

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I didn't look at your file so you have me at a disadvantage there.
    "Case near T1" is the error message?
    You are trying to use exactly what you posted even though the file path and range doesn't apply to you?
    Your version number can be found in C:\Program Files\Microsoft Office xx

    EDIT - apparently the reference to 8.0 isn't an error. I tried this using 15 and got an error about not being able to find an ISAM. After switching back to 8.0 that message is gone. At the moment it's complaining about the range value I used, so I can at least say that the file path and 8.0 reference is correct.
    Eventually I got data returned as long as there is a range defined as in A1:B1 However, I don't care for the symbols it returned (#) for decimal points nor the fact that even after I closed the query Access held a lock on the file. Methinks I would try TransferSpreadsheet instead.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Somehow the system did not like when I inserted this code into my existing subroutine

    I found a work around. I made the changes to replace the filename and sheet_name with mine and modified to add "into temp_table" ( retained the Excel 8.0)
    and used the following ..

    Docmd.Openquery "Temp_qry"

    and it worked.

    Will work on reading other segments and saving them to tables.

    Thank you so much

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. Not sure if you saw my 2 stage edit....
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Actually , I got ISAM error when I used TransferSpreadsheet so used above select statement to read different sections.
    Only issue I might face by using select statement is too many temporary table to import different sections as I have to read at least 5 sheets from each workbook for the same ranges.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the file path/names are somewhat stable, then maybe use msoFileDialogFolderPicker and do a loop in vba to run several sql statements.
    Thanks for the rep points. Wish others were as thoughtful.
    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. Replies: 3
    Last Post: 12-17-2019, 04:31 PM
  2. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  3. Export Access data to Excel as Read-only
    By rmmhie in forum Import/Export Data
    Replies: 1
    Last Post: 10-26-2013, 07:44 AM
  4. Replies: 1
    Last Post: 09-07-2011, 01:56 PM
  5. Replies: 2
    Last Post: 02-27-2010, 06:53 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