Importing Data Question

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

Hello. I have over 2000 separate Excel 2000 files that are all
formatted exactly the same way. I need to take the data from three of
the cells and put them into a table in a separate Excel worksheet.

If I open up 100 files at a time, how would I create a macro that
would add the data from each worksheet into the new destination
worksheet, allowing me to close the 100 files and then open up another
100 files which would be added onto the new worksheet, etc.

Thank you.

Steven
 
Hi Steven,
I would use a different approach.
Use the windows command window to get the filenames into a text file.

Dir >textfilename.txt

Copy & paste the names into a column.
In the next column enter ='c:\path\[" & $a$1 & "]sheetname"'!$D$47"
(Thats a double quote followed by a single quote before the !)
Change "path" to your path.
Change "sheetname" to your sheets name. (and pray they are the same in each
wb)
Change "D47" to your first remote cell.
In the next 2 columns change the cell address.

This creates a link to the data in the spreadsheets.
Do a fill down with your 3 columns to row 2000 and you have the data.
Select & copy the data, do a paste special "values" to break the links.

A1 | B1
|C1
wb1.xls ='c:\path\[" & a1 & "]sheetname"'!$D$47" ='c:\path\[" & a1
& "]sheetname"'!$K$500"

Of course, if your hobby is opening spreadsheets you can do it the long
way.;<)
 
Sorry that should be...

='c:\path\[" & a1 & "]sheetname"'!"$D$47

And be prepared to wait awhile for the remote links to update.


--
John
johnf202 at hot mail dot com


jaf said:
Hi Steven,
I would use a different approach.
Use the windows command window to get the filenames into a text file.

Dir >textfilename.txt

Copy & paste the names into a column.
In the next column enter ='c:\path\[" & $a$1 & "]sheetname"'!$D$47"
(Thats a double quote followed by a single quote before the !)
Change "path" to your path.
Change "sheetname" to your sheets name. (and pray they are the same in
each wb)
Change "D47" to your first remote cell.
In the next 2 columns change the cell address.

This creates a link to the data in the spreadsheets.
Do a fill down with your 3 columns to row 2000 and you have the data.
Select & copy the data, do a paste special "values" to break the links.

A1 | B1 |C1
wb1.xls ='c:\path\[" & a1 & "]sheetname"'!$D$47" ='c:\path\[" &
a1 & "]sheetname"'!$K$500"

Of course, if your hobby is opening spreadsheets you can do it the long
way.;<)


--
John
johnf202 at hot mail dot com


Steven said:
Hello. I have over 2000 separate Excel 2000 files that are all
formatted exactly the same way. I need to take the data from three of
the cells and put them into a table in a separate Excel worksheet.

If I open up 100 files at a time, how would I create a macro that
would add the data from each worksheet into the new destination
worksheet, allowing me to close the 100 files and then open up another
100 files which would be added onto the new worksheet, etc.

Thank you.

Steven
Click to expand...
Click to expand...
 
And the real bad news is that you'd have to use =indirect() with a string like
this. And =indirect() won't work if the sending workbook is closed.

But you could use the same sort of technique to build the formula that points
directly at the closed workbook.

So with your workbook name in A1, you could use a formula like this:

="$$$'c:\path\[" & a1 & "]sheetname'!$D$47"

(Change c:\path\ and sheetname to what's required)
and drag down the column.

This'll end up with a string that looks like:
$$$'c:\path\[book1.xls]sheetname'!$D$47

Now select this column and convert it to values
And finally make that string into a real formula:
Select that column
Edit|replace
what: $$$
with: =
replace all

I'd do a small amount of these to test. If you've made a typo and do all 2000
formulas, you'll be dismissing 2000 "where's that workbook" dialogs.




Sorry that should be...

='c:\path\[" & a1 & "]sheetname"'!"$D$47

And be prepared to wait awhile for the remote links to update.

--
John
johnf202 at hot mail dot com

jaf said:
Hi Steven,
I would use a different approach.
Use the windows command window to get the filenames into a text file.

Dir >textfilename.txt

Copy & paste the names into a column.
In the next column enter ='c:\path\[" & $a$1 & "]sheetname"'!$D$47"
(Thats a double quote followed by a single quote before the !)
Change "path" to your path.
Change "sheetname" to your sheets name. (and pray they are the same in
each wb)
Change "D47" to your first remote cell.
In the next 2 columns change the cell address.

This creates a link to the data in the spreadsheets.
Do a fill down with your 3 columns to row 2000 and you have the data.
Select & copy the data, do a paste special "values" to break the links.

A1 | B1 |C1
wb1.xls ='c:\path\[" & a1 & "]sheetname"'!$D$47" ='c:\path\[" &
a1 & "]sheetname"'!$K$500"

Of course, if your hobby is opening spreadsheets you can do it the long
way.;<)


--
John
johnf202 at hot mail dot com


Steven said:
Hello. I have over 2000 separate Excel 2000 files that are all
formatted exactly the same way. I need to take the data from three of
the cells and put them into a table in a separate Excel worksheet.

If I open up 100 files at a time, how would I create a macro that
would add the data from each worksheet into the new destination
worksheet, allowing me to close the 100 files and then open up another
100 files which would be added onto the new worksheet, etc.

Thank you.

Steven
Click to expand...
Click to expand...
Click to expand...
 
I've done something much like this to get data from Excel workbooks stored on
the West Coast when I'm on the East Coast. The rate limiting step is the
time it takes to open the files over the network. If the workbooks are
local, it runs quite fast. I have all that code on another laptop here. If
I have time, I'll find it and post it here if it's still needed.

Dave Peterson said:
And the real bad news is that you'd have to use =indirect() with a string like
this. And =indirect() won't work if the sending workbook is closed.

But you could use the same sort of technique to build the formula that points
directly at the closed workbook.

So with your workbook name in A1, you could use a formula like this:

="$$$'c:\path\[" & a1 & "]sheetname'!$D$47"

(Change c:\path\ and sheetname to what's required)
and drag down the column.

This'll end up with a string that looks like:
$$$'c:\path\[book1.xls]sheetname'!$D$47

Now select this column and convert it to values
And finally make that string into a real formula:
Select that column
Edit|replace
what: $$$
with: =
replace all

I'd do a small amount of these to test. If you've made a typo and do all 2000
formulas, you'll be dismissing 2000 "where's that workbook" dialogs.




Sorry that should be...

='c:\path\[" & a1 & "]sheetname"'!"$D$47

And be prepared to wait awhile for the remote links to update.

--
John
johnf202 at hot mail dot com

jaf said:
Hi Steven,
I would use a different approach.
Use the windows command window to get the filenames into a text file.

Dir >textfilename.txt

Copy & paste the names into a column.
In the next column enter ='c:\path\[" & $a$1 & "]sheetname"'!$D$47"
(Thats a double quote followed by a single quote before the !)
Change "path" to your path.
Change "sheetname" to your sheets name. (and pray they are the same in
each wb)
Change "D47" to your first remote cell.
In the next 2 columns change the cell address.

This creates a link to the data in the spreadsheets.
Do a fill down with your 3 columns to row 2000 and you have the data.
Select & copy the data, do a paste special "values" to break the links.

A1 | B1 |C1
wb1.xls ='c:\path\[" & a1 & "]sheetname"'!$D$47" ='c:\path\[" &
a1 & "]sheetname"'!$K$500"

Of course, if your hobby is opening spreadsheets you can do it the long
way.;<)


--
John
johnf202 at hot mail dot com


Hello. I have over 2000 separate Excel 2000 files that are all
formatted exactly the same way. I need to take the data from three of
the cells and put them into a table in a separate Excel worksheet.

If I open up 100 files at a time, how would I create a macro that
would add the data from each worksheet into the new destination
worksheet, allowing me to close the 100 files and then open up another
100 files which would be added onto the new worksheet, etc.

Thank you.

Steven
Click to expand...
Click to expand...
Click to expand...
 
I've done the same kind of thing.

Sometimes, I'd copy the files located on a network drive to my harddrive
manually--sometimes, I'd use excel's FileCopy or FSO's CopyFile to do the real
work.



Barb said:
I've done something much like this to get data from Excel workbooks stored on
the West Coast when I'm on the East Coast. The rate limiting step is the
time it takes to open the files over the network. If the workbooks are
local, it runs quite fast. I have all that code on another laptop here. If
I have time, I'll find it and post it here if it's still needed.

Dave Peterson said:
And the real bad news is that you'd have to use =indirect() with a string like
this. And =indirect() won't work if the sending workbook is closed.

But you could use the same sort of technique to build the formula that points
directly at the closed workbook.

So with your workbook name in A1, you could use a formula like this:

="$$$'c:\path\[" & a1 & "]sheetname'!$D$47"

(Change c:\path\ and sheetname to what's required)
and drag down the column.

This'll end up with a string that looks like:
$$$'c:\path\[book1.xls]sheetname'!$D$47

Now select this column and convert it to values
And finally make that string into a real formula:
Select that column
Edit|replace
what: $$$
with: =
replace all

I'd do a small amount of these to test. If you've made a typo and do all 2000
formulas, you'll be dismissing 2000 "where's that workbook" dialogs.




Sorry that should be...

='c:\path\[" & a1 & "]sheetname"'!"$D$47

And be prepared to wait awhile for the remote links to update.

--
John
johnf202 at hot mail dot com

Hi Steven,
I would use a different approach.
Use the windows command window to get the filenames into a text file.

Dir >textfilename.txt

Copy & paste the names into a column.
In the next column enter ='c:\path\[" & $a$1 & "]sheetname"'!$D$47"
(Thats a double quote followed by a single quote before the !)
Change "path" to your path.
Change "sheetname" to your sheets name. (and pray they are the same in
each wb)
Change "D47" to your first remote cell.
In the next 2 columns change the cell address.

This creates a link to the data in the spreadsheets.
Do a fill down with your 3 columns to row 2000 and you have the data.
Select & copy the data, do a paste special "values" to break the links.

A1 | B1 |C1
wb1.xls ='c:\path\[" & a1 & "]sheetname"'!$D$47" ='c:\path\[" &
a1 & "]sheetname"'!$K$500"

Of course, if your hobby is opening spreadsheets you can do it the long
way.;<)


--
John
johnf202 at hot mail dot com


Hello. I have over 2000 separate Excel 2000 files that are all
formatted exactly the same way. I need to take the data from three of
the cells and put them into a table in a separate Excel worksheet.

If I open up 100 files at a time, how would I create a macro that
would add the data from each worksheet into the new destination
worksheet, allowing me to close the 100 files and then open up another
100 files which would be added onto the new worksheet, etc.

Thank you.

Steven
Click to expand...
Click to expand...
Click to expand...
 
Back
Top