How can I copy data from a file without opening it???

[supanoods]

I currently open a .log file (like a .csv) and import it into excel to make a .xls file.

The .log file however is growing all the time and is updated from another source every ten minutes. This means that it is undesirable for the .log file to be open any length of time.

Is it possible to copy this files contents without actually opening it?

Any help or advice would be most appreciated!

Phasid "If something is hard to do - then it isn't worth doing" - Homer Simpson

 

[xlbo]

Have a look at this link - you may be able to fudge it to loop thru all cells but if not, this is the only way I know of to get data from a closed file so you may be outta luck:

http://j-walk.com/ss/excel/tips/tip82.htm

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ETID]

Create a live link to to your file from within excel,

From excel click Data>Get External Data>New Database Query>....the choose text files* from the list...

If a text driver is not in the list, then choose <new data source> and build one. (more on this if needed)


follow the wizard from there (to point to your path\file)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data that is returned and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to data comes in (if you set the refresh on open option)

 

[xlbo]

Good one ETID - I always forget you can query a text file from MSQuery Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[supanoods]

Thanks guys for your help!

However, the file i need is on our factory network - therefore I have gone with a slightly different approach. I have decided to make the VBA code copy the file and store it on the root drive of the local machine that is running my macro. Then the info is obtained from the copy - this also means that the original file is never opened. Also the code has to refresh the copied file every time the macro is run.

In case anyone is intrested I used the following subroutine:

Sub CopyFile()

Dim SourceFile
Dim DestinFile

SourceFile = &quot;**name of original file + path here**&quot;
DestinFile = &quot;**name of new file and path (C here**&quot;
FileCopy SourceFile, DestinFile ' Copy source to target.

End Sub

Thanx again
&quot;If something is hard to do - then it isn't worth doing&quot; - Homer Simpson