What's the optimal way to pull data from multiple workbooks?!?

simurq

Board Regular
Joined
Nov 11, 2011
Messages
73
I have multiple workbooks which I want to use as databases. Each of them has various data in multiple columns. Here is a sample from "Navaids.xls":

Code:
[TABLE="width: 417"]
<tbody>[TR]
[TD][B]ID[/B][/TD]
[TD][B]Name[/B][/TD]
[TD][B]Freq[/B][/TD]
[TD][B]Lat[/B][/TD]
[TD][B]Lon[/B][/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD]Williams Harbour[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]52.558889[/TD]
[TD="align: right"]-55.782222[/TD]
[/TR]
[TR]
[TD]1B[/TD]
[TD]Sable Island[/TD]
[TD="align: right"]277[/TD]
[TD="align: right"]43.930556[/TD]
[TD="align: right"]-60.022778[/TD]
[/TR]
[TR]
[TD]1D[/TD]
[TD]Charlottetown[/TD]
[TD="align: right"]346[/TD]
[TD="align: right"]52.775[/TD]
[TD="align: right"]-56.123889[/TD]
[/TR]
[TR]
[TD]1E[/TD]
[TD]Black Tickle[/TD]
[TD="align: right"]349[/TD]
[TD="align: right"]53.466667[/TD]
[TD="align: right"]-55.787222[/TD]
[/TR]
[TR]
[TD]1F[/TD]
[TD]Manta (Bathurst)[/TD]
[TD="align: right"]363[/TD]
[TD="align: right"]47.630556[/TD]
[TD="align: right"]-65.744722[/TD]
[/TR]
[TR]
[TD]1K[/TD]
[TD]Zama Lake[/TD]
[TD="align: right"]227[/TD]
[TD="align: right"]59.021944[/TD]
[TD="align: right"]-118.8225[/TD]
[/TR]
</tbody>[/TABLE]

The rest of database workbooks ("Navaids.xls", "Communications.xls", "Runways.xls", etc.) look almost the same although the data varies.

Now, I'd like to know which of the below methods is optimal for attaching these databases to a project file and being able to manipulate them through that file without opening (for best user experience)?!?

1) Save and read from the databases as CSV or XLS files
2) Save and read from the databases as TXT files

Please note that I'm planning to distribute my project within an archived (.zip or .rar) file which will contain a folder with a main project file ("Project.xlsm") and the database workbooks either within the same folder or within individual "Databases" folder. Again, I want (i) to keep the main file "Project.xlsm" as light as possible - otherwise, if I assign a separate sheet to each database in the same file, it grows up considerably; (ii) to be able to pull data from databases using popular search methods like FIND or VLOOKUP without opening the database files.

Thanks a lot in advance!!!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

simurq

Board Regular
Joined
Nov 11, 2011
Messages
73
Ok, guys, I'll ask the question in a different way:

I'll be extremely grateful (!) if you can show/guide me on HOW I can manipulate data through VBA in XML and CSV files without opening them?

Thank you very much in advance!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,652
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top