VBA: Can I move data from one workbook to another without opening the workbook?

[Peter h]

I'm just curious if I can put data into a different workbook without actually opening the workbook. I use Environ("Username") to create a list on a hidden sheet to monitor who accesses my projects. I use this method for multiple projects. Well I was thinking that maybe I'll start separate workbook to store this data (eg. "Users.xlsm"). So I was thinking about if, for example, a user opens "DataEntry.xlsm", the username, file name, and date stamp will be recorded in the "Users.xlsm" file. I know how to do this by having the code open the file, record the data, and then save and close the file, but I was wondering if it was possible to do all of that without ever actually opening the "Users" file. If the file does have to be opened, can it be done using something like .Visible = False, so that the users can't see this happening? Thanks for any help or suggestions on the matter.

 

[BrianJN1]

The MSDos file system allowed files to be appended to others without having to open the target.
I see that VBA may be probably do similar with text files. If they are delimited then you could draw that data into your workbook at a later time, but I cannot image that you do as you desire without having a workbook already open.
My thoughts only.

 

[Fazza]

Reading & writing to closed files is routine with ADO - working with Excel (or other files) in the same way as with databases.

INSERT INTO can append data.

This old thread from another forum explains it very well Using ADO with Excel files - Xtreme Visual Basic Talk

Google will find much more info, including old Mr Excel threads.

Be sure to use the correct connection string. Such as at https://www.connectionstrings.com

 

[Peter h]

Thanks for the comments and thoughts guys. I'll look more into it tonight.

 

[Fazza]

Simplest, if it suits, might be to write to a txt file.

i thought finding an example online would be easy but it took me a few minutes. Save and Retrieve Information With Text Files - Peltier Tech Blog

if you have a VBA book, try looking there too