Office Automation - Excel Spreadsheets -Open / Close

[JDB]

I am trying to write a subroutine that reads data line by line from an
Excel spreadsheet.

Questions:

Do I have to Open the Excel spreadsheet each time or can access get
the data without opening the spreadsheet?

Can I open the spreadsheet as read only? At the end of the
subroutine I get a msg box asking me if I want to save the opened
Excel file.

Thanks

JDB

 

[Mr. B]

Hi, JDB.

You will have to open the Excel file. However, you do not have to have that
opened file to be visible to users. You can just open the file and read the
data you need to read, performing the necessary actions with that data, and
then simply close that Excel file without saving any changes, and you can do
all of it without ever having Excel be visible at all.

There are a lot of threads in this newsgroup that describe this type of data
retreival form Excel and even provide explicit code for do these actions.

Try doing a search for "open instance of excel" or something like it and
take a look at the information that is available.

 

[Ken Snell MVP]

Write Data From an EXCEL Worksheet into a Recordset using Automation
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#WriteFileRst

 

[JDB]

Thanks, very helpful on the key words to search for;

also helpful was the thread

-http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

Thanks again

JDB

 

[JDB]

Thanks for the site
Excellent info

I am just a hacker so always need to re-understand ojects, and you
site gives some good examples.

Thanks again

JDB

 

[David H]

Anytime I'm doing automation with an application, I always start by building
the code within the application to the extent that I can as the VBA Help file
will be right there - not to mention that there's always a true Object Model
available that spells out everything graphically along with examples. Using a
ready made SUB saves a lot of time, but if you don't dig into what's going on
then you're still left with having problems on the next go around.

When I'm playing in the other app, I'll even go so far as to create a simple
Sub with a STOP statement so that I can snoop around the object model using
the immediate window...as in

Sub test()

stop

end test

From there, I'll add in code as I've figured it outl.