I have a large number of excel files and I want to use a macro to upload the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number of
files is so large that this is too time consuming.

Is it possible that setting up links to the other spreadhseets would work?
That way you could just click "Yes" to update links when opening the one
spreadsheet.

Best wishes,

Jim

"Excel Macros" wrote:

I have a large number of excel files and I want to use a macro to upload the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number of
files is so large that this is too time consuming.

How would I set up links to the other spreadsheets, I have over 2000 excel
spreadsheets to upload into one spreadsheet.

"Jim Jackson" wrote:

Is it possible that setting up links to the other spreadhseets would work?
That way you could just click "Yes" to update links when opening the one
spreadsheet.

Best wishes,

Jim

"Excel Macros" wrote:

I have a large number of excel files and I want to use a macro to upload the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number of
files is so large that this is too time consuming.

You might do better to create an Access database with tables linked to the
spreadsheets. Access should be able to handle such a large task more
efficiently and more quickly.

I Access you can click on "File - Get External Data - Linked Tables." The
process will create the table for you so all you need do is assign a name.

Either way, initially, it will take a great deal of time.

If I come across a better solution I will post it here.

Jim
"Excel Macros" wrote:

How would I set up links to the other spreadsheets, I have over 2000 excel
spreadsheets to upload into one spreadsheet.

"Jim Jackson" wrote:

Is it possible that setting up links to the other spreadhseets would work?
That way you could just click "Yes" to update links when opening the one
spreadsheet.

Best wishes,

Jim

"Excel Macros" wrote:

I have a large number of excel files and I want to use a macro to upload the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number of
files is so large that this is too time consuming.

Can you post your current macro?
It should just be a question of running that in a loop...

--
Tim Williams
Palo Alto, CA


"Excel Macros" <Excel wrote in message ...
I have a large number of excel files and I want to use a macro to upload the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number of
files is so large that this is too time consuming.

I gather that you want some code to access each file in turn, run your
macro, then close that file and open the next file. Is that right?
The code below will do that if all the files are in the same folder and you
want to run your macro with each file in that folder. If you want to
exclude some of the files, additional code will be required. Note that this
macro opens each file in turn. It doesn't make that file the active file.
If your code is written to operate on only the active file, add the
following line before calling your macro:
Windows(TheFile).Activate
Change the path as necessary.
Please post back and advise us all on how this works or doesn't work for
you. HTH Otto
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
Call YourMacroHere
wb.Close
TheFile = Dir
Loop
End Sub

"Excel Macros" <Excel wrote in message
...
I have a large number of excel files and I want to use a macro to upload
the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number
of
files is so large that this is too time consuming.

Otto, how do I modify to include subfolders and only run the macro on
the most recently modified file in the subfolder. TIA

Greg
Otto Moehrbach wrote:
I gather that you want some code to access each file in turn, run your
macro, then close that file and open the next file. Is that right?
The code below will do that if all the files are in the same folder and you
want to run your macro with each file in that folder. If you want to
exclude some of the files, additional code will be required. Note that this
macro opens each file in turn. It doesn't make that file the active file.
If your code is written to operate on only the active file, add the
following line before calling your macro:
Windows(TheFile).Activate
Change the path as necessary.
Please post back and advise us all on how this works or doesn't work for
you. HTH Otto
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
Call YourMacroHere
wb.Close
TheFile = Dir
Loop
End Sub

"Excel Macros" <Excel wrote in message
...
I have a large number of excel files and I want to use a macro to upload
the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number
of
files is so large that this is too time consuming.

Greg
That question is above my head. Perhaps someone else will pitch in and
provide you an answer. HTH Otto
"GregR" wrote in message
ups.com...
Otto, how do I modify to include subfolders and only run the macro on
the most recently modified file in the subfolder. TIA

Greg
Otto Moehrbach wrote:
I gather that you want some code to access each file in turn, run your
macro, then close that file and open the next file. Is that right?
The code below will do that if all the files are in the same folder and
you
want to run your macro with each file in that folder. If you want to
exclude some of the files, additional code will be required. Note that
this
macro opens each file in turn. It doesn't make that file the active
file.
If your code is written to operate on only the active file, add the
following line before calling your macro:
Windows(TheFile).Activate
Change the path as necessary.
Please post back and advise us all on how this works or doesn't work for
you. HTH Otto
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
Call YourMacroHere
wb.Close
TheFile = Dir
Loop
End Sub

"Excel Macros" <Excel wrote in message
...
I have a large number of excel files and I want to use a macro to upload
the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The
number
of
files is so large that this is too time consuming.

How many levels of subfolders? Just one level under a main folder?

Tim

"GregR" wrote in message ups.com...
Otto, how do I modify to include subfolders and only run the macro on
the most recently modified file in the subfolder. TIA

Greg
Otto Moehrbach wrote:
I gather that you want some code to access each file in turn, run your
macro, then close that file and open the next file. Is that right?
The code below will do that if all the files are in the same folder and you
want to run your macro with each file in that folder. If you want to
exclude some of the files, additional code will be required. Note that this
macro opens each file in turn. It doesn't make that file the active file.
If your code is written to operate on only the active file, add the
following line before calling your macro:
Windows(TheFile).Activate
Change the path as necessary.
Please post back and advise us all on how this works or doesn't work for
you. HTH Otto
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
Call YourMacroHere
wb.Close
TheFile = Dir
Loop
End Sub

"Excel Macros" <Excel wrote in message
...
I have a large number of excel files and I want to use a macro to upload
the
data from these files into another excel spreadsheet. The macro that I
currently use requires that I upload these files one at a time. The number
of
files is so large that this is too time consuming.