Dir Path Code Problem

[Todd Huttenstine]

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).Range("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

 

[Frank Kabel]

Hi todd
to change the drive you have to use
ChDrive

ChDir will only change the directory in the current drive

 

[Todd Huttenstine]

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