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!!!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,277
Messages
5,836,358
Members
430,423
Latest member
lord_traust

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