Importing data from a spreadsheet - Read Only

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I am trying to import data from an Excel spreadsheet into
access. It works OK as long has someone in not in the
Excel spreadsheet. I can open the Excel spreadhseet "read-
only" thru Excel but I nothing I know to try works in
Access. I tried using DoCmd.Transferspreadsheet but it
bombs when somone is in the Excel file. (works OK if no
one is using the file. I tried doing a FileCopy first to
make a copy of the Excel file then do a
Transferspreadsheet from my new copy. The Filecopy bombs.
Is there a way to do this?

Thanks.
 
You likely would need to use Automation to open the EXCEL file in read-only
mode, then you could read the cells and write them to a recordset that is
based on the table that is to receive the data. Much more involved but it
won't bomb when someone has the file open.
 
Hi Ken,

This isn't an area I'm familiar with; but does it make a difference
whether the Excel workbook is set up for shared use?
 
Good question, John. I've not tried it with a workbook that is set for
shared use. I have tried this with regular workbook setups and it works
fine.

I may have to do some testing....... ;-)
 
I am not sure what you mean by "use Automation to open
the EXCEL file in read-only" Please elaborate.

Thanks.
 
Automation is the use of VBA code to open and manipulate other Microsoft
applications. In VBA code, you can start EXCEL, open workbooks, write into
cells or read cells, etc. Not overly complicated, but takes more code than
the "simpler" things.

Here's some sample code for opening an EXCEL workbook in "read only" mode:

Public Sub TestMacroRun()
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
' put code here to write into the cells etc.
' .
' .
' .
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
End Sub
 
Back
Top