I am confused, Start again

Tell me exactly what you want to do


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Geoff" wrote in message ...
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" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Geoff" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Geoff" wrote in message ...
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?