Results 1 to 14 of 14
  1. #1
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480

    From VBA, link to shared excel file, identify a specific sheet and columns.


    Hello,

    I am trying to link to an excel sheet that is stored on a network drive. I have do this in VBA.

    I want to link to a spreadsheet, a certain sheet and be able to get a row count and then cycle through the rows until a list is processed. I tried searching on the forums but wasn't able to find anything.


    Thanks for any help, I really appreciate it.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    linking to a specific Excel sheet:

    docmd.transferspreadsheet aclink,, TableName, ExcelFileName, true, SheetName!

    then you can just cycle through the linked table using a loop

    Code:
    dim db as database
    dim rst as recordset
    
    set db = currentdb
    set rst = db.openrecordset("LinkedTableName")
    
    do while rst.eof <> true
        'do whatever while cycling through records
    
        rst.movenext
    loop
    
    rst.close
    set rst = nothing
    set db = nothing

  3. #3
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Thank you so much, now I just need to figure out how to work with specific columns in the record set. Within the loop, I need to pull data from 3 different columns and punch them in other places. I know I've done this in the past but I cannot find the related code.


    * Edit, also I don't know what the tablename is its an excel file. I have the excel file name, file location and sheet name. Any tips?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you can reference the column either by it's index or, if your excel file has column headers import the sheet with the column headers intact.

    if you're referencing the first column using an index it's rst.field(0)
    if you're referencing by the column name it's rst.fields("fieldname")

    The table name is whatever you want the linked table to be named I usually use something like 'TempTable' and run code to remove it after my code is done running

  5. #5
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    I keep getting error 424 object required. Here is my blippit of code.

    Code:
    '
     Link to excel file, and sheet.
    docmd.transferspreadsheet aclink, , "Temp", "File.xlsx", "C:\Users\Username\Desktop", True, "Sheet3!A2:AU5000"
    Set DB = currentdb
    Set RST = DB.OpenRecordset("Temp")

    Everything I'm seeing looks correct... but I'm obviously missing something.

  6. #6
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Gah, sorry the error is popping up on the docmd.transferspreadsheet aclink line

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    your file name isn't supposed to be separated by a comma the file name should be the full path and name of the file

    "c:\users\username\desktop\file.xslx"

    Also, I wouldn't define a range unless you have to otherwise if you go beyond the column or row count you won't capture all the data.

  8. #8
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Yea, still getting the same error. I don't really understand why.

    Code:
    docmd.transferspreadsheet aclink, , "Temp", "C:\Users\username\Desktop\file.xlsx", True, "Sheet3!"
    Set DB = currentdb
    Set RST = DB.OpenRecordset("Temp")

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Example:

    redbull.zip

    Relies on both the database and the excel file being in the same folder.

  10. #10
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    I will have to check it when I get home, I'm at work and can not download any zip files

  11. #11
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Well, I think I found the problem.

    MSAccess object library

    is not available.

    Apparently no one in this company even has access loaded onto the machines. So I can't use the DoCmd command at all, this is going to really really suck.

    Thanks for your help, I will mark this as solved.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes you can

    You just need the MS access runtime components, they're free to download and will allow you to run the database.

  13. #13
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Sorry for the crazy delay. I have a question about using the runtime components. If I do that and build something using docmd's and such...

    Then I copy this file and let other people open it, will they also need the .dll file on their c: to run the code?


    I'm doing my work in a system called reflections now.. so its kind of different. Instead of everyone running an mde file, they are running saved instances of the emulator on a shared drive...

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you intend the database to run on someone else's computer that does not have a full version of ms access, yes, you will need to have them install the runtime components or the database won't work.

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

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2015, 08:07 AM
  2. Replies: 1
    Last Post: 11-20-2014, 08:34 AM
  3. Export to Excel as a shared file
    By ran-d in forum Programming
    Replies: 7
    Last Post: 07-23-2013, 04:04 PM
  4. Exporting to Specific Excel Sheet
    By unrealtb in forum Access
    Replies: 2
    Last Post: 01-24-2012, 10:32 PM
  5. Export Query to Specific Sheet and Rows/Columns
    By chewbears in forum Queries
    Replies: 7
    Last Post: 11-30-2011, 09:44 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