Update excel data without opening data source

[dedark05]

Hi all,

I have 1 spreedsheet that summarize data from (linked to) 20 different file source. It pains me opening all file sources and wait for up to 5mins to update my data. my question is, is it possible to update my speerdsheet without opening it's data sources?

Hope somebody could help.

Regards,
Dean

[6StringJazzer]

What kind of update are you doing? Why do you have to open the files to update your data?

Some functions require the external file to be open, or will return an error. Is that what is happening to you? But how does that affect your ability to update your data?

[dedark05]

What kind of update are you doing? Why do you have to open the files to update your data?

Some functions require the external file to be open, or will return an error. Is that what is happening to you? But how does that affect your ability to update your data?

I would just like to open/update only my main spreed sheet that summarized all 20(and counting) external data sources linked. These external sources are generated by a certain software it generates/updates(overwrites old existing files) the file every 15mins.

The problem is;

1. Opening all these 20 at the same time takes time for about more than 5mins and sometimes the excel crashes.
2. I only have less than 10mins to update,calculate, and analyze my main spreed sheet, and then close all that 20 opened external file source because as I mentioned, these external file will be overwrite/replaced with an updated file by the 3rd party software so these 20 files needs to be closed.
3. The process repeats every time the files sources update.

All these problem can be solve if I can just update my spreed sheet without opening the said external file sources.

[6StringJazzer]

Can't you open the file and update it without opening the other 20 files? If not, why not?

[dedark05]

Can't you open the file and update it without opening the other 20 files? If not, why not?

As I said. the file is linked to the other 20 files. It needs the data from the 20 files to update.

[dedark05]

Is this possible or I am just asking too much out of excel?

[Kalithro]

It isn't impossible. I do this all the time using ADODB.

How is your data structured on the 20 files? Do they have headers, in tables, common worksheet names?

[Kalithro]

Here is an example.

One module called DBSS (Database Spreadsheet).
Other module has two subs in it, one using SQL to pull all data from a worksheet and another to pull a few headers.

I wish I had more info to help you. Check out some YouTube on SQL and ADODB, it will make pulling data so much faster for you (you don't need to open the Excel files anymore).

I do this at work with over 500 files all 20 MB or larger. Does the work in seconds.

[dedark05]

It isn't impossible. I do this all the time using ADODB.

How is your data structured on the 20 files? Do they have headers, in tables, common worksheet names?

I don't know anything about ADODB and on how it works. is it still within excel?

My 20 files are purely raw data. It has headers and the data are updated/added based on date and time. Actually these files are in *.txt format. when open as excel, worksheet names are just their file names.

Please see attached 1 sample of the 20 files attached.

[dedark05]

Here is an example.

One module called DBSS (Database Spreadsheet).
Other module has two subs in it, one using SQL to pull all data from a worksheet and another to pull a few headers.

I wish I had more info to help you. Check out some YouTube on SQL and ADODB, it will make pulling data so much faster for you (you don't need to open the Excel files anymore).

I do this at work with over 500 files all 20 MB or larger. Does the work in seconds.

Wow, very interesting. I don't know anything about SQL or ADODB. Can you give me some youtube links here?

This is how my excel files works;

1. I have the main excel files to calculate and summarized (this composed of formulas and links) daily,monthly,yearly data from the different sources I mentioned.
2. File sources are generated/updated automatically.
3. Open main file.
4. Open all linked sources.(takes time and sometime crashes) --> where the problem is
5. Update main file (takes sometimes to calculate vast amount of data) --> not really a big problem
6. then close all excel files after main file updated.

It is really a problem for me opening so many files because it takes time to calculates and sometimes excel crashes.

[Kalithro]

Yes, try this https://www.youtube.com/watch?v=HE9CIbetNnI
He goes though updating Access and Excel worksheets (without having to open them).

Check out all of WiseOwl's stuff, he is very clear and has some amazing VBA examples (ones you can download as well).

Sounds like you can stick with your import process with your text files. The main file can simply use ADODB to pull all the data from the 20 files.

[dedark05]

Yes, try this https://www.youtube.com/watch?v=HE9CIbetNnI
He goes though updating Access and Excel worksheets (without having to open them).

Check out all of WiseOwl's stuff, he is very clear and has some amazing VBA examples (ones you can download as well).

Sounds like you can stick with your import process with your text files. The main file can simply use ADODB to pull all the data from the 20 files.

Thanks for the link, Kalithro.

So in my case no SQL to be involved here just ADODB? I'll try to check the link you gave if it meets my requirements.

Many thanks, Kalithro. All add reputation for you now, and will mark this thread as solved is the info you provided helps. thanks again!

[dedark05]

Still welcome to any other solution if there's any.

[Kalithro]

I'm not sure what you need, if it is 20 Excel files (ADODB is best) or 20 Txt files (filesystemobject works) that have data.

I created this short program that collects your txt files into one worksheet. It assumes that all the text files are like the one you linked and that they are all in a folder that you can select (or hardcode in).

I made it an xlsb file type to keep file size down when you collect all your data.

ADODB requires you to use SQL. SQL is good to learn.

[dedark05]

I'm not sure what you need, if it is 20 Excel files (ADODB is best) or 20 Txt files (filesystemobject works) that have data.

I created this short program that collects your txt files into one worksheet. It assumes that all the text files are like the one you linked and that they are all in a folder that you can select (or hardcode in).

I made it an xlsb file type to keep file size down when you collect all your data.

ADODB requires you to use SQL. SQL is good to learn.

Hi Kalithro,

Thanks for the info. I will check your file attached and see how it works.

In the meantime, Please check my actual files in this dropbox link to see how my files where linked and functions. (paste the folder in your drive D: root for the files to work)

https://www.dropbox.com/sh/gor2hax6g...UuP1nIYRa?dl=0

The file in xlsm format is my main file and the txts files are its data sources.

[dedark05]

I'm not sure what you need, if it is 20 Excel files (ADODB is best) or 20 Txt files (filesystemobject works) that have data.

I created this short program that collects your txt files into one worksheet. It assumes that all the text files are like the one you linked and that they are all in a folder that you can select (or hardcode in).

I made it an xlsb file type to keep file size down when you collect all your data.

ADODB requires you to use SQL. SQL is good to learn.

Hi Kalithro,

Checked and tried to run the file you attached. I really like the idea on how it works. It copied the data from source file without opening it which is what I wanted to do in the first place.

Is it possible to automate or schedule the "Get Data" function?
also, I have another (different from the 20 files I mentioned) txt file that updates but fills only 5 days of data, when this file updates it overwrites old data each time it stores a new record. with this, do you have the code in which instead of copying all data in the source file, it will just copy new data without replacing old data based on date and time? This could extend the 5 days archiving of data.

Moreover, I am now thinking about how I can apply the xlsb file you attached to my existing mainfile with least changes or without actually overhauling my formulas applied.

[dedark05]

_ _