Ref the line: MyPath = "C:\" in the sub below (from Mike H)
how can I make the path (the "C:\" bit) as a variable for the sub/Excel to
pick up? The path will be different everyday. I'd navigate to the desired
folder upon running the sub (via a "FileOpen"), then the sub will do the
rest of the processes on the files in that folder. Thanks.

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
Dim wbNew As Workbook
Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=MyPath & "1234.xls"
For x = 1 To 4
Workbooks.Open Filename:=MyPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True
End Sub

Change

MyPath = "C:\"

to this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Show
mypath = fd.SelectedItems(1)

"Max" wrote:

Ref the line: MyPath = "C:\" in the sub below (from Mike H)
how can I make the path (the "C:\" bit) as a variable for the sub/Excel to
pick up? The path will be different everyday. I'd navigate to the desired
folder upon running the sub (via a "FileOpen"), then the sub will do the
rest of the processes on the files in that folder. Thanks.

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
Dim wbNew As Workbook
Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=MyPath & "1234.xls"
For x = 1 To 4
Workbooks.Open Filename:=MyPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True
End Sub

Nearly forgot, also change:

wbNew.SaveAs Filename:=MyPath & "1234.xls"


to just

wbNew.SaveAs Filename:=MyPath

"Sam Wilson" wrote:

Change

MyPath = "C:\"

to this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Show
mypath = fd.SelectedItems(1)

"Max" wrote:

Ref the line: MyPath = "C:\" in the sub below (from Mike H)
how can I make the path (the "C:\" bit) as a variable for the sub/Excel to
pick up? The path will be different everyday. I'd navigate to the desired
folder upon running the sub (via a "FileOpen"), then the sub will do the
rest of the processes on the files in that folder. Thanks.

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
Dim wbNew As Workbook
Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=MyPath & "1234.xls"
For x = 1 To 4
Workbooks.Open Filename:=MyPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True
End Sub

Thanks Sam. I'm afraid I had difficulties trying out your response.
My original thread where Mike responded is at: http://tinyurl.com/yewknly
Could I trouble you to check out my objectives/feedback to Mike (but I
received no further reply from Mike) over there, which might explain it
better? I need to fire the sub from somewhere independent (eg from
Personal.xls), then "point" it to the particular folder with the 4 files
(1.xls, 2.xls, 3.xls & 4.xls) and then leave the sub to do the rest of the
job ... creating a 1234.xls, saving this file into that particular folder,
and stack up all the data from 1.xls, 2.xls, 3.xls & 4.xls.

Sub LoopThroughDirectory()

Application.DisplayAlerts = False

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Show
mypath = fd.SelectedItems(1) & "\"

Dim wbNew As Workbook
Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=MyPath & "1234.xls"
For x = 1 To 4
Workbooks.Open Filename:=MyPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True
End Sub

"Max" wrote:

Thanks Sam. I'm afraid I had difficulties trying out your response.
My original thread where Mike responded is at: http://tinyurl.com/yewknly
Could I trouble you to check out my objectives/feedback to Mike (but I
received no further reply from Mike) over there, which might explain it
better? I need to fire the sub from somewhere independent (eg from
Personal.xls), then "point" it to the particular folder with the 4 files
(1.xls, 2.xls, 3.xls & 4.xls) and then leave the sub to do the rest of the
job ... creating a 1234.xls, saving this file into that particular folder,
and stack up all the data from 1.xls, 2.xls, 3.xls & 4.xls.

Many thanks, Sam. Tested using your revision on the sub and it works fine in
picking it up from the dialog, and proceeding from there.

Could I have your help here on the last bit, that stacking part carried out
by Mike's
Sub DoSomething(Book As Workbook). I've pasted below the entire routine
which I just tested. Somehow the stacking sub misses capturing 2 lines, which
I checked were the last data lines in 1.xls and 2.xls. For info, my test
source 3.xls had zero data lines (it had only the row1 col headers). Thanks

-------------------------
Sub LoopThroughDirectory()

Application.DisplayAlerts = False

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Show
myPath = fd.SelectedItems(1) & "\"

Dim wbNew As Workbook
Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=myPath & "1234.xls"
For x = 1 To 4
Workbooks.Open Filename:=myPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True
End Sub

Sub DoSomething(Book As Workbook)
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

If ActiveWorkbook.Name = "1.xls" Then
ActiveSheet.Rows("1:" & lastrow).Copy
Else
ActiveSheet.Rows("2:" & lastrow).Copy
End If
lastrowNew = Windows("1234.xls").ActiveSheet.Cells(Cells.Rows.C ount,
"B").End(xlUp).Row
Windows("1234.xls").ActiveSheet.Range("A" & lastrowNew).PasteSpecial
End Sub

Max, modified the macro to display the SaveAsDialog, and assign that file
path as myPath for you to proceed...Try and feedback

Sub LoopThroughDirectory()
Dim wbNew As Workbook, myPath As String, varFile As Variant

Application.DisplayAlerts = False
varFile = Application.GetSaveAsFilename
If varFile = False Then Exit Sub
myPath = Left(varFile, InStrRev(varFile, "\"))

Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=varFile
For x = 1 To 4
Workbooks.Open Filename:=myPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
' DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Max" wrote:

Ref the line: MyPath = "C:\" in the sub below (from Mike H)
how can I make the path (the "C:\" bit) as a variable for the sub/Excel to
pick up? The path will be different everyday. I'd navigate to the desired
folder upon running the sub (via a "FileOpen"), then the sub will do the
rest of the processes on the files in that folder. Thanks.

Sub LoopThroughDirectory()
Application.DisplayAlerts = False
'Change this to your directory
MyPath = "C:\"
Dim wbNew As Workbook
Set wbNew = Workbooks.Add()
wbNew.SaveAs Filename:=MyPath & "1234.xls"
For x = 1 To 4
Workbooks.Open Filename:=MyPath & x & ".xls"
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
Next
Application.DisplayAlerts = True
End Sub

Thanks Jacob. Afraid I had difficulties trying it out. My original thread
where Mike responded is at: http://tinyurl.com/yewknly
Could I trouble you to check out my objectives/feedback to Mike (but I
received no further reply from Mike) over there, which might explain it
better? I need to fire the sub from somewhere independent (eg from
Personal.xls), then "point" it to the particular folder with the 4 files
(1.xls, 2.xls, 3.xls & 4.xls) and then leave the sub to do the rest of the
job ... creating a 1234.xls, saving this file into that particular folder,
and stack up all the data from 1.xls, 2.xls, 3.xls & 4.xls.

I thought I have responded..OK...I have read your responses to Mike in
MSwebnewsreader itself. If I understand you correctly the below would belp

'Modify your Sub as below; So that myPath is an argument be passed while
calling 'this sub
Sub LoopThroughDirectory(myPath as String)
'your code
End Sub

'From personal.xls
LoopThroughDirectory "C:\temp\"


If this post helps click Yes
---------------
Jacob Skaria


"Max" wrote:

Thanks Jacob. Afraid I had difficulties trying it out. My original thread
where Mike responded is at: http://tinyurl.com/yewknly
Could I trouble you to check out my objectives/feedback to Mike (but I
received no further reply from Mike) over there, which might explain it
better? I need to fire the sub from somewhere independent (eg from
Personal.xls), then "point" it to the particular folder with the 4 files
(1.xls, 2.xls, 3.xls & 4.xls) and then leave the sub to do the rest of the
job ... creating a 1234.xls, saving this file into that particular folder,
and stack up all the data from 1.xls, 2.xls, 3.xls & 4.xls.