Dir Path Code Problem
 

Const foldername = "Q:\Stats2004\Contract Reports\Daily
Renewal Audits\March"
'HERE IS THE MAPPED PATH Const foldername = "\\scgvlfs05
\sesfa\Stats2004\Contract Reports\Daily Renewal
Audits\March"

Sub CommandButton1_Click()
Dim FName As String
Dim WB As Workbook
Dim dest As Variant
Dim numcount As Long
Dim numcount2 As Long

numcount = Application.WorksheetFunction.CountA(Workbooks
("abc.xls").Worksheets(1).Range("A:A")) + 2
dest = ("A" & numcount)
ChDir foldername
FName = Dir("*.xls")
MsgBox foldername

Do Until FName = ""
Set WB = Workbooks.Open(FName)
numcount2 = Application.WorksheetFunction.CountA(Workbooks
("abc.xls").Worksheets(1).Range("A:A")) + 2

WB.Worksheets(1).Range("A1:B10").Copy
Destination:=Workbooks("abc.xls").Worksheets(1).Ra nge("A"
& numcount2)
WB.Close savechanges:=False

FName = Dir()
Loop
End Sub

The above code works fine when everything is in a
directory on the C drive but when I tried to change the
path to Q:\ by changing Const foldername = "Q:\Stats2004
\Contract Reports\Daily Renewal Audits\March" it messes
up. For some reason its looking in the P drive and
pulling files from there. I have no idea why its looking
in P instead of Q. I even changed the Const foldername
= "\\scgvlfs05\sesfa\Stats2004\Contract Reports\Daily
Renewal Audits\March" (which is the name of the mapped
drive) and still does not work. Also when I intentionally
changed the path to invalid path the code told me it was
invalid path so I know the code sees the "Q:\Stats2004
\Contract Reports\Daily Renewal Audits\March" which I
specified to be valid. Can anyone please help me?

Thank you

Todd Huttenstine

Dir Path Code Problem
 

Hi todd
to change the drive you have to use
ChDrive

ChDir will only change the directory in the current drive


--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
Const foldername = "Q:\Stats2004\Contract Reports\Daily
Renewal Audits\March"
'HERE IS THE MAPPED PATH Const foldername = "\\scgvlfs05
\sesfa\Stats2004\Contract Reports\Daily Renewal
Audits\March"

Sub CommandButton1_Click()
Dim FName As String
Dim WB As Workbook
Dim dest As Variant
Dim numcount As Long
Dim numcount2 As Long

numcount = Application.WorksheetFunction.CountA(Workbooks
("abc.xls").Worksheets(1).Range("A:A")) + 2
dest = ("A" & numcount)
ChDir foldername
FName = Dir("*.xls")
MsgBox foldername

Do Until FName = ""
Set WB = Workbooks.Open(FName)
numcount2 = Application.WorksheetFunction.CountA(Workbooks
("abc.xls").Worksheets(1).Range("A:A")) + 2

WB.Worksheets(1).Range("A1:B10").Copy
Destination:=Workbooks("abc.xls").Worksheets(1).Ra nge("A"
& numcount2)
WB.Close savechanges:=False

FName = Dir()
Loop
End Sub

The above code works fine when everything is in a
directory on the C drive but when I tried to change the
path to Q:\ by changing Const foldername = "Q:\Stats2004
\Contract Reports\Daily Renewal Audits\March" it messes
up. For some reason its looking in the P drive and
pulling files from there. I have no idea why its looking
in P instead of Q. I even changed the Const foldername
= "\\scgvlfs05\sesfa\Stats2004\Contract Reports\Daily
Renewal Audits\March" (which is the name of the mapped
drive) and still does not work. Also when I intentionally
changed the path to invalid path the code told me it was
invalid path so I know the code sees the "Q:\Stats2004
\Contract Reports\Daily Renewal Audits\March" which I
specified to be valid. Can anyone please help me?

Thank you

Todd Huttenstine

Dir Path Code Problem
 

Alright thank you. That worked. I was going nuts over
here.

-----Original Message-----
Hi todd
to change the drive you have to use
ChDrive

ChDir will only change the directory in the current drive


--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
Const foldername = "Q:\Stats2004\Contract Reports\Daily
Renewal Audits\March"
'HERE IS THE MAPPED PATH Const foldername = "\\scgvlfs05
\sesfa\Stats2004\Contract Reports\Daily Renewal
Audits\March"

Sub CommandButton1_Click()
Dim FName As String
Dim WB As Workbook
Dim dest As Variant
Dim numcount As Long
Dim numcount2 As Long

numcount = Application.WorksheetFunction.CountA
(Workbooks
("abc.xls").Worksheets(1).Range("A:A")) + 2
dest = ("A" & numcount)
ChDir foldername
FName = Dir("*.xls")
MsgBox foldername

Do Until FName = ""
Set WB = Workbooks.Open(FName)
numcount2 = Application.WorksheetFunction.CountA
(Workbooks
("abc.xls").Worksheets(1).Range("A:A")) + 2

WB.Worksheets(1).Range("A1:B10").Copy
Destination:=Workbooks("abc.xls").Worksheets(1).Ra nge
("A"
& numcount2)
WB.Close savechanges:=False

FName = Dir()
Loop
End Sub

The above code works fine when everything is in a
directory on the C drive but when I tried to change the
path to Q:\ by changing Const foldername = "Q:\Stats2004
\Contract Reports\Daily Renewal Audits\March" it messes
up. For some reason its looking in the P drive and
pulling files from there. I have no idea why its
looking
in P instead of Q. I even changed the Const foldername
= "\\scgvlfs05\sesfa\Stats2004\Contract Reports\Daily
Renewal Audits\March" (which is the name of the mapped
drive) and still does not work. Also when I
intentionally
changed the path to invalid path the code told me it was
invalid path so I know the code sees the "Q:\Stats2004
\Contract Reports\Daily Renewal Audits\March" which I
specified to be valid. Can anyone please help me?

Thank you

Todd Huttenstine
.