Excel 2003
From my notes I have: (PathOnlySource is the full path to the folder holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears to be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do. Where am I
going wrong? Thanks for your help. Otto

Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the TheFile = Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears to be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do. Where am
I
going wrong? Thanks for your help. Otto

Thanks Tom. I'll work with this and see why I'm not looping through the
files with my code. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the TheFile =
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears to be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do. Where
am
I
going wrong? Thanks for your help. Otto

Tom
My code is the same as yours but mine didn't work last night. Now it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History. I am
looping through the files in the New folder. For each file I want to ask
the question: "Is this file (same name) in the History folder?" The only
thing I know to do is to loop through all the files in the History folder
and compare file names. Is there a better way? Maybe Find? Thanks for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the TheFile =
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears to be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do. Where
am
I
going wrong? Thanks for your help. Otto

just get a list of the history files and put them in an array, then loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last night. Now it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History. I am
looping through the files in the New folder. For each file I want to ask
the question: "Is this file (same name) in the History folder?" The only
thing I know to do is to loop through all the files in the History folder
and compare file names. Is there a better way? Maybe Find? Thanks for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the TheFile =
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do. Where
am
I
going wrong? Thanks for your help. Otto

Tom
Thanks for taking your time to do that. That will go a long way to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask of copying
a file from New folder to History folder. I was deep into SaveAs statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a file. I
tried it and used the same file name but changed the path from New to
History. It didn't just copy the file, it moved it, which was just what I
wanted. Do you see anything inherently wrong with moving the file from New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array, then loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last night. Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History. I am
looping through the files in the New folder. For each file I want to
ask
the question: "Is this file (same name) in the History folder?" The
only
thing I know to do is to loop through all the files in the History
folder
and compare file names. Is there a better way? Maybe Find? Thanks for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do.
Where
am
I
going wrong? Thanks for your help. Otto

No - there should be no problems. That is the command to move a file in
VBA.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Thanks for taking your time to do that. That will go a long way to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask of
copying
a file from New folder to History folder. I was deep into SaveAs
statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a file.
I
tried it and used the same file name but changed the path from New to
History. It didn't just copy the file, it moved it, which was just what I
wanted. Do you see anything inherently wrong with moving the file from
New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array, then loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last night. Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History. I
am
looping through the files in the New folder. For each file I want to
ask
the question: "Is this file (same name) in the History folder?" The
only
thing I know to do is to loop through all the files in the History
folder
and compare file names. Is there a better way? Maybe Find? Thanks
for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the
TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the
folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do.
Where
am
I
going wrong? Thanks for your help. Otto

Tom
Thanks a bunch. You have given me a valuable lesson in a part of VBA
(working with files, file names, and folders) that I knew little about.
Otto
"Tom Ogilvy" wrote in message
...
No - there should be no problems. That is the command to move a file in
VBA.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Thanks for taking your time to do that. That will go a long way to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask of
copying
a file from New folder to History folder. I was deep into SaveAs
statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a file.
I
tried it and used the same file name but changed the path from New to
History. It didn't just copy the file, it moved it, which was just what
I
wanted. Do you see anything inherently wrong with moving the file from
New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array, then
loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last night.
Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History. I
am
looping through the files in the New folder. For each file I want
to
ask
the question: "Is this file (same name) in the History folder?" The
only
thing I know to do is to loop through all the files in the History
folder
and compare file names. Is there a better way? Maybe Find? Thanks
for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the
TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the
folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it
appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do.
Where
am
I
going wrong? Thanks for your help. Otto

Tom
I had a small problem with your code. I parsed the code here to
describe the problem.
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
MsgBox Nw(i)
Loop
ReDim Nw(1 To i)
MsgBox Nw(1) & " " & Nw(2) & " " & Nw(3)

The MsgBox inside the loop, at the end of the loop, shows all 3 files (there
are only 3 files), one at a time. But the MsgBox at the end shows a blank.
If I Remark-out the Redim command line, the MsgBox shows all 3 files.
I don't know enough about the Redim of an array to spot the problem. I know
you do. Thanks for the help. Otto
"Tom Ogilvy" wrote in message
...
No - there should be no problems. That is the command to move a file in
VBA.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Thanks for taking your time to do that. That will go a long way to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask of
copying
a file from New folder to History folder. I was deep into SaveAs
statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a file.
I
tried it and used the same file name but changed the path from New to
History. It didn't just copy the file, it moved it, which was just what
I
wanted. Do you see anything inherently wrong with moving the file from
New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array, then
loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last night.
Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History. I
am
looping through the files in the New folder. For each file I want
to
ask
the question: "Is this file (same name) in the History folder?" The
only
thing I know to do is to loop through all the files in the History
folder
and compare file names. Is there a better way? Maybe Find? Thanks
for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the
TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the
folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it
appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't do.
Where
am
I
going wrong? Thanks for your help. Otto

Tom
I found it. I was looking through John Walkenbach's book and saw that I
have to put "Redim Preserve Nw(1 To i) to preserve the existing values of
the array. Thanks again. Otto
"Otto Moehrbach" wrote in message
...
Tom
I had a small problem with your code. I parsed the code here to
describe the problem.
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
MsgBox Nw(i)
Loop
ReDim Nw(1 To i)
MsgBox Nw(1) & " " & Nw(2) & " " & Nw(3)

The MsgBox inside the loop, at the end of the loop, shows all 3 files
(there
are only 3 files), one at a time. But the MsgBox at the end shows a
blank.
If I Remark-out the Redim command line, the MsgBox shows all 3 files.
I don't know enough about the Redim of an array to spot the problem. I
know
you do. Thanks for the help. Otto
"Tom Ogilvy" wrote in message
...
No - there should be no problems. That is the command to move a file in
VBA.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Thanks for taking your time to do that. That will go a long way
to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask of
copying
a file from New folder to History folder. I was deep into SaveAs
statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a
file.
I
tried it and used the same file name but changed the path from New to
History. It didn't just copy the file, it moved it, which was just
what
I
wanted. Do you see anything inherently wrong with moving the file
from
New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array, then
loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last night.
Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and History.
I
am
looping through the files in the New folder. For each file I want
to
ask
the question: "Is this file (same name) in the History folder?"
The
only
thing I know to do is to loop through all the files in the History
folder
and compare file names. Is there a better way? Maybe Find?
Thanks
for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the
TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to the
folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it
appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't
do.
Where
am
I
going wrong? Thanks for your help. Otto

Yes, my oversight. Apologies.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
I found it. I was looking through John Walkenbach's book and saw that
I
have to put "Redim Preserve Nw(1 To i) to preserve the existing values of
the array. Thanks again. Otto
"Otto Moehrbach" wrote in message
...
Tom
I had a small problem with your code. I parsed the code here to
describe the problem.
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
MsgBox Nw(i)
Loop
ReDim Nw(1 To i)
MsgBox Nw(1) & " " & Nw(2) & " " & Nw(3)

The MsgBox inside the loop, at the end of the loop, shows all 3 files
(there
are only 3 files), one at a time. But the MsgBox at the end shows a
blank.
If I Remark-out the Redim command line, the MsgBox shows all 3 files.
I don't know enough about the Redim of an array to spot the problem. I
know
you do. Thanks for the help. Otto
"Tom Ogilvy" wrote in message
...
No - there should be no problems. That is the command to move a file
in
VBA.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Thanks for taking your time to do that. That will go a long way
to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask of
copying
a file from New folder to History folder. I was deep into SaveAs
statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a
file.
I
tried it and used the same file name but changed the path from New
to
History. It didn't just copy the file, it moved it, which was just
what
I
wanted. Do you see anything inherently wrong with moving the file
from
New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array,
then
loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count,
1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last
night.
Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and
History.
I
am
looping through the files in the New folder. For each file I
want
to
ask
the question: "Is this file (same name) in the History folder?"
The
only
thing I know to do is to loop through all the files in the
History
folder
and compare file names. Is there a better way? Maybe Find?
Thanks
for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do the
TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to
the
folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it
appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it doesn't
do.
Where
am
I
going wrong? Thanks for your help. Otto

No apologies needed. I'm amazed that you put this whole thing together as
quick as you did. Thanks. Otto
"Tom Ogilvy" wrote in message
...
Yes, my oversight. Apologies.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
I found it. I was looking through John Walkenbach's book and saw
that
I
have to put "Redim Preserve Nw(1 To i) to preserve the existing values
of
the array. Thanks again. Otto
"Otto Moehrbach" wrote in message
...
Tom
I had a small problem with your code. I parsed the code here to
describe the problem.
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
MsgBox Nw(i)
Loop
ReDim Nw(1 To i)
MsgBox Nw(1) & " " & Nw(2) & " " & Nw(3)

The MsgBox inside the loop, at the end of the loop, shows all 3 files
(there
are only 3 files), one at a time. But the MsgBox at the end shows a
blank.
If I Remark-out the Redim command line, the MsgBox shows all 3 files.
I don't know enough about the Redim of an array to spot the problem.
I
know
you do. Thanks for the help. Otto
"Tom Ogilvy" wrote in message
...
No - there should be no problems. That is the command to move a
file
in
VBA.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Thanks for taking your time to do that. That will go a long
way
to
solving this OP's problem.
If I could backtrack a little. I was working on the subtask
of
copying
a file from New folder to History folder. I was deep into SaveAs
statements
when I looked at:Name oldpathname As newpathname
that you had given me for the task of changing the name of a
file.
I
tried it and used the same file name but changed the path from New
to
History. It didn't just copy the file, it moved it, which was
just
what
I
wanted. Do you see anything inherently wrong with moving the file
from
New
to History by this means? Thanks again. Otto

"Tom Ogilvy" wrote in message
...
just get a list of the history files and put them in an array,
then
loop
through the array or use Match for the comparison

The below is untested, so it may contain syntax errors/typos but
represents
a general approach

Sub CopyData()
Dim His() As String
Dim Nw() As String
Dim sPathHis As String, sPathNw As String
Dim i As Long, res As Variant
Dim rng As Range, rng1 As Range
Dim sNew As String, sHis As String
Dim wkbk As Workbook
sPathHis = "C:\History\"
sPathNw = "C:\New\"

ReDim His(1 To 1000)
ReDim Nw(1 To 1000)

' get a list from history

sHis = Dir(sPathHis & "*.xls")
i = 0
Do While sHis < ""
i = i + 1
His(i) = sHis
sHis = Dir
Loop
ReDim His(1 To i)

' get a list from new

sNew = Dir(sPathNw & "*.xls")
i = 0
Do While sNew < ""
i = i + 1
Nw(i) = sNew
sNew = Dir
Loop
ReDim Nw(1 To i)

' now process all the files in history

For i = 1 To UBound(Nw)
res = Application.Match(Nw(i), His, 0)
If Not IsError(res) Then
' code to copy data
Set wkbk = Workbooks.Open(sPathNw & Nw(i))
Set rng = wkbk.Worksheets(1).Range("A1").CurrentRegion
rng.Copy
Application.DisplayAlerts = False
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
Set wkbk = Workbooks.Open(sPathHis & Nw(i))
Set rng1 = wkbk.Worksheets(1).Cells(Rows.Count,
1).End(xlUp)(2)
rng1.Paste
wkbk.Close SaveChanges:=True
Else
FileCopy sPathNw & Nw(i), sPathHis & Nw(i)
End If
' Kill sPathNw & nw(i)
Next

End Sub


--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Tom
My code is the same as yours but mine didn't work last
night.
Now
it
does, as does yours. Senility is a weird thing.
Now I have another question. I have folders New and
History.
I
am
looping through the files in the New folder. For each file I
want
to
ask
the question: "Is this file (same name) in the History
folder?"
The
only
thing I know to do is to loop through all the files in the
History
folder
and compare file names. Is there a better way? Maybe Find?
Thanks
for
your help. It is very much appreciated. Otto
"Tom Ogilvy" wrote in message
...
Sub Tester9()
PathOnlysource = "C:\Data2"

ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile < ""
Debug.Print TheFile
TheFile = Dir
Loop

End Sub

works fine for me. You want to look at the file, then do
the
TheFile
=
Dir

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in
message
...
Excel 2003
From my notes I have: (PathOnlySource is the full path to
the
folder
holding
the files)
ChDir PathOnlySource
TheFile = Dir(PathOnlySource & "\*.xls")
Do While TheFile < ""
TheFile = Dir
MsgBox TheFile
Loop

I thought that the line:
TheFile = Dir(PathOnlySource & "\*.xls")
would get the first .xls file name in the folder, which it
appears
to
be
doing. And I thought that the line:
TheFile = Dir
Would get subsequent file names which it appears it
doesn't
do.
Where
am
I
going wrong? Thanks for your help. Otto