Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009

    Unanswered: Read Excel data into Access

    I wrote an Access 2003 VBA routine that reads upwards of 20 Excel files into an Access table (one at a time) using the DOCMD.TransferSpreadsheet method (DAO).

    The code works great, except when one of the spreadsheets is open for editing by someone else, at which time I get an error message and my code stops running. I have absolutely no control over this happening, or any way to know ahead of time when it is happening.

    I want only to read the spreadsheet data into an Access table, I don't want to write to it, or look at it visually. I don't even really want to open it, in the normal sense, just get in, read from it, and get out.

    Is there a way to access the spreadsheet in Read Only mode?

    I found a response here that appears to be close:

    Private Sub CmdOpenExcel_Click()
    'WillR - opens the specified Spreadsheet
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("R:\path\path2\path3\xlfilename.xl s", , False)
    End With
    End Sub

    (I changed the False to a True to get the spreadsheet in Read Only)

    There's a lot I don't understand about this method; for instance, when it opens the spreadsheet, how do I get the data to my access table? My TransferSpreadsheet method just puts it right in there.

    Also, how do I close Excel once I've opened it like this? I put the command '.Workbooks.Close' in the With block, and it closes the spreadsheet but not Excel. Every time I exercise the routine, I get another instance of Excel.

    Thank you ahead of time for your response.

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia
    Have you tried just linking your spreadsheets to your database?

  3. #3
    Join Date
    Oct 2009
    Linking to the spreadsheet won’t work.

    This is a strange grouping of users. Mid management puts resource forecast information into the spreadsheets and upper management uses the database for analysis. Lots of users on both sides, very dynamic situation.

    Besides, when I start up the database, I don’t know which spreadsheets are going to be read. Maybe one, maybe some, maybe all.

  4. #4
    Join Date
    Jul 2004
    South Dakota
    Don't know if this would work but I thought that I would throw it out there.

    Is it possible to put in some code to copy all the spreadsheets into a temp folder where you could read them in from there and then delete them when done? I don't know if you can copy an open file or not but if you could this might be a work around that wouldn't take a total overhaul of your app.


  5. #5
    Join Date
    Oct 2009
    I'd thought of that myself. I can put some code into the spreadsheets so that when they are closed they put a copy of themselves into a temporary directory.

    I think the idea is kind of kludgie, but right now, that's all I have.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts