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.

Greg,
This might help.
Tim

'###########################################
Option Explicit

Sub ProcessNewestFiles()

Const S_PATH As String = "D:\Analysis"

Dim oFSO As Scripting.FileSystemObject
Dim foldSub As Folder, sFile As String

Set oFSO = New Scripting.FileSystemObject
For Each foldSub In oFSO.GetFolder(S_PATH).SubFolders
sFile = GetLastModified(foldSub.Path)
If sFile < "" Then
'*****************
'process sFile here
Debug.Print foldSub.Path & " " & sFile
'*****************
End If
Next foldSub

End Sub

'Given as folder path, return the path of the most-recently
' modified file (or "" if no files are found.
Function GetLastModified(sDirPath As String) As String

Dim oFSO As Scripting.FileSystemObject
Dim lastDate As Date
Dim f As File, retVal As String

Set oFSO = New Scripting.FileSystemObject
retVal = ""
lastDate = CDate("01/01/1901")
For Each f In oFSO.GetFolder(sDirPath).Files
If f.Name Like "*.xls" Then
If f.DateLastModified lastDate Then
lastDate = f.DateLastModified
retVal = f.Path
End If
End If
Next f

GetLastModified = retVal

End Function
'##########################################


"GregR" wrote in message ups.com...
Tim, that is correct only one level of subfolders

Greg
Tim Williams wrote:
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

Tim, you just about have it. The only missing part is I want to open
the most recent modified file in each subfolder. TIA

Greg
Tim Williams wrote:
Greg,
This might help.
Tim

'###########################################
Option Explicit

Sub ProcessNewestFiles()

Const S_PATH As String = "D:\Analysis"

Dim oFSO As Scripting.FileSystemObject
Dim foldSub As Folder, sFile As String

Set oFSO = New Scripting.FileSystemObject
For Each foldSub In oFSO.GetFolder(S_PATH).SubFolders
sFile = GetLastModified(foldSub.Path)
If sFile < "" Then
'*****************
'process sFile here
Debug.Print foldSub.Path & " " & sFile
'*****************
End If
Next foldSub

End Sub

'Given as folder path, return the path of the most-recently
' modified file (or "" if no files are found.
Function GetLastModified(sDirPath As String) As String

Dim oFSO As Scripting.FileSystemObject
Dim lastDate As Date
Dim f As File, retVal As String

Set oFSO = New Scripting.FileSystemObject
retVal = ""
lastDate = CDate("01/01/1901")
For Each f In oFSO.GetFolder(sDirPath).Files
If f.Name Like "*.xls" Then
If f.DateLastModified lastDate Then
lastDate = f.DateLastModified
retVal = f.Path
End If
End If
Next f

GetLastModified = retVal

End Function
'##########################################


"GregR" wrote in message ups.com...
Tim, that is correct only one level of subfolders

Greg
Tim Williams wrote:
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

What I posted doesn't do that?
The example code checks every subfolder under "D:\Analysis" and finds the most-recently modified Excel file in each folder. Remove
the check for "*.xls" or modify if there are other types of file you want to work with.

Is there some way other than using the timestamp on the file by which you're identifying "most recent" ?

--
Tim Williams
Palo Alto, CA


"GregR" wrote in message ups.com...
Tim, you just about have it. The only missing part is I want to open
the most recent modified file in each subfolder. TIA

Greg
Tim Williams wrote:
Greg,
This might help.
Tim

'###########################################
Option Explicit

Sub ProcessNewestFiles()

Const S_PATH As String = "D:\Analysis"

Dim oFSO As Scripting.FileSystemObject
Dim foldSub As Folder, sFile As String

Set oFSO = New Scripting.FileSystemObject
For Each foldSub In oFSO.GetFolder(S_PATH).SubFolders
sFile = GetLastModified(foldSub.Path)
If sFile < "" Then
'*****************
'process sFile here
Debug.Print foldSub.Path & " " & sFile
'*****************
End If
Next foldSub

End Sub

'Given as folder path, return the path of the most-recently
' modified file (or "" if no files are found.
Function GetLastModified(sDirPath As String) As String

Dim oFSO As Scripting.FileSystemObject
Dim lastDate As Date
Dim f As File, retVal As String

Set oFSO = New Scripting.FileSystemObject
retVal = ""
lastDate = CDate("01/01/1901")
For Each f In oFSO.GetFolder(sDirPath).Files
If f.Name Like "*.xls" Then
If f.DateLastModified lastDate Then
lastDate = f.DateLastModified
retVal = f.Path
End If
End If
Next f

GetLastModified = retVal

End Function
'##########################################


"GregR" wrote in message ups.com...
Tim, that is correct only one level of subfolders

Greg
Tim Williams wrote:
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