Reading from closed excel file into word form and writing data to closed excel file

[Kym Evans]

Hi,

Was wondering if anybody could help. In the Initialise_Form subroutine of a
word macro, I would like to read data (a number) from a single cell in
Excel, insert it as the caption of a label box (so that the data cannot be
altered by the user), then add 1 to the number in the cell in Excel and save
the worksheet, all without opening the worksheet. This will ensure that
each time the form is initialised, it will have a unique number. The reason
why I would like to use Excel is that there will be a number of users
initialising forms. I have seen the excellent tips on the MVP website (and
have used them to load a listbox from an excel range for another part of the
form), but cannot find anything directly on point. I would like to know
whether saving data to a closed Excel file can be done. Any help would be
appreciated.

Many thanks,

Kym

 

[Rob]

In order to save a file, a copy of it has to be open. It seems like using an
Access database would be better for this. If you insist on using Excel, why
not just open the app invisible, do your thing, and close it?

 

[Tony Jollans]

Prior to 2007, the only sure way to read data from an Excel file was via
Excel - in theory one could unscramble the compound file format and extract
the BIFF format stream from it and then unscramble that and extract cell
data from it and, again in theory and certainly more riskily, one could
write back to the 'closed' file - but none of what you need is documented
and it would probably be quicker, and certainly be easier, just to open an
instance of Excel. With the new .xlsx and .xlsm file formats in 2007, you
can read and write with any tool that can process xml.