Variable Drive Letter - Import.csv

[wazza]

Hi

I have a peice of code i use to import a .csv spreadsheet.

doCmd.TransferText acImportDelim,
"DailySpec", "c:\myfolder\import\daily_a.csv", False, ""


The drive letter C:\ may sometimes change according to the user specification
My module gets the specified letter - how do i use the variable in the previous statement...

ive tried many ways: including..



getDIR = D 'gets the specified drive
DIRAddress = getDIR & ":\myfolder\import\daily_a.csv", False, ""

doCmd.TransferText acImportDelim,
"DailySpec", DIRAddress, ""



Where am i going wrong - ive looked at this too long!!

Many thanks

 

[KenHigg]

How does your module get the drive letter?

kh

 

[wazza]

the user inputs the drive letter on a form - when updated the drive letter is stored in a table. This module collects the drive letter...

from here i wanted to assign this varible drive letter to a variable name and use in the import statement... and having syntax difficulty

rgrds

 

[KenHigg]

Hum...

Still don't fully understand...

Maybe you just missed the quotes around the D in the example you posted...

getDIR = "D" 'gets the specified drive
DIRAddress = getDIR & ":\myfolder\import\daily_a.csv", False, ""

kh

 

[wazza]

when the database is installed on either a laptop, stand-alone machine or even on a network they sometimes use different drive letters. c:/, d:/, h:/

wherever the user is - im trying to give them the option to change the directory to import some files

this was my orginal import statment which worked locally:
doCmd.TransferText acImportDelim,
"DailySpec", "C:\myfolder\import\daily_a.csv", False, ""


** I notice there are quotes around the dir location.
When using a varible name i tried assigning the full path to varible like so...

getDIR = D 'gets the specified drive
DIRAddress = getDIR & ":\myfolder\import\daily_a.csv", False, ""


'or with quotes which i cant get working
DIRAddress1 = """ & DIRAddress & """


'with final import statement being:----
doCmd.TransferText acImportDelim,
"DailySpec", DIRAddress1, False, ""

 

[wazza]

oh sorry... I have used quotes in my actual vb:

getDIR = "D"

 

[KenHigg]

When you say:

getDIR = D 'gets the specified drive

This looks like you are assigning a value of 'D' to a variable name getDIR.

Or is getDIR a function that should return say, 'D'?

???
kh

 

[KenHigg]

You should leave the false stuff off of the end of the string:

DIRAddress = getDIR & ":\myfolder\import\daily_a.csv"

kh

 

[wazza]

I have tried the following but no luck;



'*** getDIR is assigned to data "C" from looking at the table entry

'*getDIR = "C"
getDIR = rstDIR.fields("Local_Admin")

'*complete string for path
DIRAddress = getDIR & ":\myfolder\imports\daily_a.csv"

'*with final import statement being:----
doCmd.TransferText acImportDelim,
"DailySpec", DIRAddress, False, ""



are there quotes required - before and after the DIRAddress in the import statement..? How would these be placed?

 

[wazza]

an error message appears:

"wrong data type"

 

[KenHigg]

But it works like this:

getDIR = "C"
'*getDIR = rstDIR.fields("Local_Admin")

'*complete string for path
DIRAddress = getDIR & ":\myfolder\imports\daily_a.csv"

'*with final import statement being:----
doCmd.TransferText acImportDelim,
"DailySpec", DIRAddress, False, ""


???
kh

 

[wazza]

no i cannot get either working
getDIR = "C" or getDIR = rstDIR.fields("Local_Admin")

does the import statement not need quotes around the 'DIRAddress'?

My orginal import statment specified the path with quotes eiter end...
"C\myfolder\import\daily_a.csv"
however the new variable im replacing the path with has none..

??

 

[KenHigg]

When you use a string var in place of the actual string you leave the quotes out. The only other thing I can see is the DailySpec thing. Have you used this before?


kh

 

[wazza]

ok i thought so... i have used the spec before, its the saved import specification for that particular file.

i still get the error msg "wrong data type"... it must be the way i use the variable in the statement

 

[KenHigg]

I would pull everything out except the bare minimums, like:

Try:

DIRAddress = "C:\myfolder\imports\daily_a.csv"
doCmd.TransferText ,,, DIRAddress

If that works then, then:

getDIR = "C"
DIRAddress = getDIR & ":\myfolder\imports\daily_a.csv"
doCmd.TransferText ,,, DIRAddress

Then just keep adding stuff on...

kh

 

[wazza]

Thanks very much for your help...

I finally got it working - the statement was correct - where ive been messing with the code iv accidently removed the file name it should pick up.

many thanks

 

[KenHigg]

Cool - Glad you got to work - I was gettin worried