Path error in code

[Michael Abrams]

Previously, Hans supplied the code to do this;

Code: Select all

Private Sub cmdImportCAP_Click()

Dim strMonth As String
strMonth = InputBox("Enter 3 letter abbreviation for month.")
If Not Len(strMonth) = 3 Then
MsgBox "Invalid - try again.", vbExclamation
Exit Sub
End If


DoCmd.TransferText acImportFixed, "CAP 2007", "CAP ", _
"R:SMGR HMOS 2009\AETNA 2009\& strmonth &\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

Beep
    
MsgBox "CAP FILE IMPORTED !!!", vbInformation, "CAP DATA IMPORTED"

DoCmd.Close

End Sub

except when I asked for help, I gave this line:
"R:SMGR HMOS 2009AETNA 2009 MAR 2009CAP FILESSPECIALTY CAPG0034V00.txt", False, ""
when I should have supplied this line:
"R:SMGR HMOS 2009\AETNA 2009\MAR 2009\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""
(the slashes were left out on the original post by me)
I am now receiving the error message ".....path not found...."
What do I need to modify to get this to work?

Thank you.

Michael

[Wendell]

Looks to me like your line should read:
"R:\SMGR HMOS 2009\AETNA 2009\MAR 2009\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

[Michael Abrams]

Looks to me like your line should read:
"R:\SMGR HMOS 2009\AETNA 2009\MAR 2009\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

Here is what i am trying to do (I fixed the code - it was a bad C&P originally by me)

Instead of
"R:SMGR HMOS 2009\AETNA 2009\MAY 2009\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

I want to use the input box (strmonth) as follows:

"R:SMGR HMOS 2009\AETNA 2009\& strmonth &\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

Path not found. Am I using the & in the correct place?

[Michael Abrams]

Here is the fix:

"R:SMGR HMOS 2009\AETNA 2009"\& strmonth &"\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

I needed a couple of well placed quotes.

Thanks !!

Michael

[HansV]

I assume that you meant

"R:SMGR HMOS 2009\AETNA 2009\" & strmonth & "\CAP FILES\SPECIALTY CAP\G0034V00.txt", False, ""

i.e. the first closing quote is AFTER the backslash instead of before it.

[Michael Abrams]

Just seeing if you were looking..........

Actually, I had it right in the code on the form, but typed it here instead of C&P.

Thanks for all the help all the time !!

Michael

[Wendell]

I'm a bit puzzled - I was assuming that "R:" was referring to a network drive of some sort, and in general a path need to be formed as "R:\" in such cases. But I now see what you were trying to do with the variable month.

[Michael Abrams]

I'm a bit puzzled - I was assuming that "R:" was referring to a network drive of some sort, and in general a path need to be formed as "R:\" in such cases. But I now see what you were trying to do with the variable month.

Not an easy day for me today

I missed the R:\ on one of the posts above - so you assumed correctly .

But the world is better now, and I do thank you for following up.

I will REALLY try to be more careful next time - I hate wasting your time like that. I do know how important it is putting every 'dot and dash' in it's proper place.

Again, thank you.

Michael

[Wendell]

Not to worry - I've just spent several days on an issue that appeared to be an Access bug - turns out there was a function I didn't know about that would have solved the problem for me...