Looping through excel files to add to a new workbook

[Guest]

Hi I need to loop through a series of excel files stored in a folder on my C
drive, add data to the spreadsheet, select the modified data range (number of
rows differs from spreadsheet to spreadsheet) and sequentially enter this
data into a new spreadsheet to be imported into an Access table. Can anyone
out there help?

 

[Ron de Bruin]

Start here
http://www.rondebruin.nl/tips.htm

 

[Guest]

Thanks for pointing me in the right direction, I'm pretty sure that the "Copy
a range from all files in a folder" (Example_4) should give me what I need,
but I must be doing something wrong.

I'm running the application from a command button on an Access form and I've
referenced both the Microsoft ActiveX Data Objects 2.5 Library and the
Microsoft Excel 11.0 Object Library. The Excel workbooks have been placed
into a file C:\Data and the Example_4 code has been placed in the Click event
of a command button on the form, while the functions and GetData macro have
been placed in an Access module (Module1).

However the Application.ScreenUpdating method fails when I run the code, and
if this code is commented out then the Activeworkbook.Worksheets.Add method
fails and no new workbook is opened or updated. (I actually want the data
inserted into an Access table - the consolidated new workbook is just an
interim step in the process prior to importing it into Access).

Also the range of the data to import from each file varies according to the
number of records on each individual Excel worksheet selected. How do I get
around this?

Or am I on the wrong track and should I maybe be using one of the other
examples? Thanks for your help.

 

[Ron de Bruin]

Hi Geoff

Run the code from Excel and then import the workbook into Excel

See
http://www.rondebruin.nl/copy3.htm

And use this one
http://www.rondebruin.nl/copy3.htm#header

 

[Guest]

Hi Ron

Thanks for your assistance so far, but I'm still battling with the problem.
Both your Example1 and FSO_Example_1 extract the file names perfectly when
run in Excel, but from there I get lost. Which of the examples should I be
using to transfer ranges of data (A7 to last file) from sheet 1 of each of
the file names extracted from the folder into a new worksheet? Or is there a
single Example that combines the extraction of the file names from the folder
and then inserts the selected range of data from each closed workbook in the
folder consecutively into a new workbook?

Because when I now run Example4 (copy cells from all workbooks in a folder)
I get the error (ambiguous name) for the Lastrow function. Your help has been
great so far but I'm new to this and still confused.

Many thanks

 

[Ron de Bruin]

I am confused, Start again

Tell me exactly what you want to do

 

[Guest]

OK Ron, I get around 80 Excel workbooks (timesheets) per month which all get
saved in a single folder on the C Drive. On sheet 1 of each of these
workbooks I have to manipulate data (Change column names, copy and paste data
from a cell into new columns and change data (hours - a derived field) that
is in time format to numeric format).

Then I have to copy a range of data from sheet 1 of each of these
(manipulated) worksheets into a new workbook with a single set of column
headings. The header row is extracted from row A7 to H7 and the data from row
A8 to cell H of the last row of data in the worksheet. The number of rows
varies from workbook to workbook.

The consolidated workbook then must be imported into an Access table from
which I run parameter queries by client as to the number of hours worked per
client for billing purposes. Printed time sheets (Access reports) are then
extracted for each client based on the number of employee hours worked per
contract.

I don't have a problem with the Access stuff, nor now in extracting the
workbook names from the folder on the C Drive (thanks to you), nor in
importing the consolidated workbook into Access. My only problem is in
creating the single Excel workbook from the 70 file names extracted and
inserting the varying ranges of data into this consolidated spread sheet
prior to importing it into Access.

Hope this explains the problem? I am sure too that you have given me the
solution - it's just that I'm not sure which of the various examples to use
that would do what I need. Thanks for all your help so far - you've been
great!

 

[Ron de Bruin]

OK, try this

First change the data in the workbooks
You can find a example here
http://www.rondebruin.nl/copy4.htm

Then if you done that use this to retrieve the data
http://www.rondebruin.nl/copy3.htm#header

with this
Set sourceRange = mybook.Worksheets(1).Range("A7:H" & lrow)

 

[Guest]

Thanks Ron this is great stuff - I've been able to do exactly what I need
thanks to your assistance. If you're ever in South Africa give me a call on
0834460095 and I'll buy you a meal!

 

[Ron de Bruin]

Hi Geoff

You are welcome
Thanks for the feedback