Connections or import

[Lasse Moe]

Hi.

I need to set up a connection between two excel workbooks. The source workbook is located on a network drive and is updated with fresh data every knight. The workbooks name, network path, and structure never changes. It always consists of the same 11 columns with headers in row 7. Number or rows (data) varies between 2000 and 50000. Attached file “source” is my example source workbook.

Locally on my computer I have a workbook with a sheet that is an exact copy of the same sheet in the source workbook. I use the data to present various statistics in other sheets. Attached file “local” is my example local workbook.

This is what I need help with:
Import new data from sheet1 source workbook to sheet2 in local workbook. With import I mean replace all data from row 8 and downwards in sheet2 local workbook with data in sheet1 source workbook. I would like to do this manually via a macro button in sheet1 local workbook. If possible I would also like “a time and date stamp” that informs me when I last updated.

[Alf]

Perhaps a macro like this?

Please Login or Register  to view this content.

This macro assumes that file "source.xlsx" is opened. I've changed the name of "local.xlsx" to "local.xlsm" so you can add a macro to this file.


Alf

[Lasse Moe]

Thanks Alf. I`ll try this later today.

Is there no way to import data from a closed file?

[Alf]

Is there no way to import data from a closed file?

Oh yes that is possible but it depends a bit of what you whish to do. Importing a small bit of information from a number of files it will probably be faster than opening the file, importing data and closing file.

But if you whish to read a lot of data form one file it will be faster to just open the file, read the data and close it but then your boss may not be so impressed as when you read data from a closed file.

This is one link about how to do it:

http://spreadsheetpage.com/index.php...a_closed_file/

Alf

[Lasse Moe]

I`ve implemented your code Alf, but it doesn`t seem to work properly. The code won`t copy the data from source to local.

Can you have look?

Regards
Lasse

[Alf]

Strange as I tested your two sample files and the code worked ok. I'll upload the two sample files I worked with so you can test those in your environment and see what happens.

Alf