Ask a geek!

[Raging_Geek]

Hello,

I am the web master of bc-girls.com and you can ask me any question you like about programming, web pages, and/or computers, tec.

I don't know everything but I do have lots of creditials and lots of experience so ask what you like and I will do my best to answer you.

Take care.

 

[primetime]

What do you know about VBA and ADO within Microsoft Excel. I am looking for a macro that will go through a folder of about 300 files and multiple tabs. I need read specific data from each tab except the summary tab. The information I need on each tab is located in the same cells on each sheet.

Below is a link of a what I am trying to accomplish, but not sure how to revise it for my situation.

http://www.exceltip.com/at-429|Import_and_Export_in_VBA-Import_data_from_a_closed_workbook_%28ADO%29_using_VBA_in_Microsoft_Excel

 

[Raging_Geek]

VBA in Excel

Hi PrimeTime,

I could not find the article in the link that you mention however I think I might be able to help you....

if you know all file names you are after you could try something like this:
-----------------------------------
fname = "filename.xls"
set wbook = Workbooks.Open(Filename:=fname)
---------------------------------------------
that will open a workbook

now to get the data from the cell in the tab (i.e. worksheet) of that workbook...
-----------------------------------------------------------------------------
x = wbook.Worksheets("tabname").range("A1").value
--------------------------------------------------------------------------
that will get the value of the cell A1 from the worksheet tab named "tabname"

or you can access each tab (i..e worksheet) numerically
like this:

-----------------------------------------------------------------------------
x = wbook.Worksheets(1).range("B2").value
-----------------------------------------------------------------------
that will get the value of the cell B2 from the 1st worksheet tab.

now put all the file names in an array and you should be able to loop thru each file and with a little logic to avoid the summary tab you should be able access the exact cell data you require.

Hope that helps - let me know if you need more...

raging geek