DLookup Path problem

[gmazza via AccessMonster.com]

Hey there,
Instead of changing the code all the time, I stored my path in my table and
am using DLookup to grab the path for Importing.
Here is my code:
strFile = Nz(DLookup("Directory450", "ImportDirectory"))
DoCmd.TransferText acImportDelim, "450ImportSpecs", "450Export", strFile

Here is the value of the field Directory450:
DIR("F:\IT\db\Sales\Archive\450*.csv")

It is failing on my import saying you cannot import this file. I figure it is
because when I debug I see the strFile = "DIR("F:\IT\db\Sales\Archive\450*.
csv")"

It is putting quotes around the value in the table. I don';t think I want
this, any thoughts??
Thanks!!

 

[Linq Adams via AccessMonster.com]

My guess would be because your file name is

450*.csv

and files names cannot have special characters, such as the asterisk, in them.

 

[gmazza via AccessMonster.com]

How would I get the filenames then that have characters after the 450. The
characters will change behind the 450 so I want to make sure it grabs them
all.

 

[gmazza via AccessMonster.com]

Thats not the problem anyway as I just changed the filename to 450aa.csv
which does exist and it still said you cannot import this file.

 

[Douglas J. Steele]

strFile would need to be "F:\IT\db\Sales\Archive\450aa.csv" in that case
(i.e.: you do not want the reference to the Dir function). Is it?

In answer to your other question, you need code like:

Dim strFolder As String
Dim strFile As String

strFolder = ""F:\IT\db\Sales\Archive\"
strFile = Dir(strFolder & "450*.csv")
Do While Len(strFile) > 0
DoCmd.TransferText acImportDelim, "450ImportSpecs", "450Export",
strFolder & strFile
strFile = Dir()
Loop

 

[gmazza via AccessMonster.com]

Thanks again Douglas, works perfect! Now the only problem is my export that I
replied to an earlier message you wrote me. It said this:
Thanks Douglas.
I changed it and now I am gettting an error for my code below:
strFile = "P:\Navision ERP\MEI Files\ToNav\SCMHeader" & Format(Sequence,
"0000000") & ".txt"
DoCmd.TransferText acExportDelim, "455ExportSpecs", "SCMHeader", strFile,
True

Error is: Tge Microsoft Office Access database engine could not find the
object 'SCMHEADER0000001#txt'. Make sure the object exists and that you spell
its name and the path correctly.

I even tried ditching the strfile and I just put a normal pat in of P:\
SCMHeader.txt and I get the same error, this time saying SCMHeader#txt can't
be found.

I don't understand this error? I don't need to find it, I want it exported to
this file.
Thanks!

strFile would need to be "F:\IT\db\Sales\Archive\450aa.csv" in that case
(i.e.: you do not want the reference to the Dir function). Is it?

In answer to your other question, you need code like:

Dim strFolder As String
Dim strFile As String

strFolder = ""F:\IT\db\Sales\Archive\"
strFile = Dir(strFolder & "450*.csv")
Do While Len(strFile) > 0
DoCmd.TransferText acImportDelim, "450ImportSpecs", "450Export",
strFolder & strFile
strFile = Dir()
Loop

Thats not the problem anyway as I just changed the filename to 450aa.csv
which does exist and it still said you cannot import this file.

[quoted text clipped - 5 lines]

 

[Douglas J. Steele]

The error message implies that you've got

& "#txt"

not

& ".txt"

I'm confused, though. I showed you how to find all the .csv files starting
with 455 in a particular folder. Why are you using an extension of txt?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks again Douglas, works perfect! Now the only problem is my export
that I
replied to an earlier message you wrote me. It said this:
Thanks Douglas.
I changed it and now I am gettting an error for my code below:
strFile = "P:\Navision ERP\MEI Files\ToNav\SCMHeader" & Format(Sequence,
"0000000") & ".txt"
DoCmd.TransferText acExportDelim, "455ExportSpecs", "SCMHeader", strFile,
True

Error is: Tge Microsoft Office Access database engine could not find the
object 'SCMHEADER0000001#txt'. Make sure the object exists and that you
spell
its name and the path correctly.

I even tried ditching the strfile and I just put a normal pat in of P:\
SCMHeader.txt and I get the same error, this time saying SCMHeader#txt
can't
be found.

I don't understand this error? I don't need to find it, I want it exported
to
this file.
Thanks!

strFile would need to be "F:\IT\db\Sales\Archive\450aa.csv" in that case
(i.e.: you do not want the reference to the Dir function). Is it?

In answer to your other question, you need code like:

Dim strFolder As String
Dim strFile As String

strFolder = ""F:\IT\db\Sales\Archive\"
strFile = Dir(strFolder & "450*.csv")
Do While Len(strFile) > 0
DoCmd.TransferText acImportDelim, "450ImportSpecs", "450Export",
strFolder & strFile
strFile = Dir()
Loop

Thats not the problem anyway as I just changed the filename to 450aa.csv
which does exist and it still said you cannot import this file.

[quoted text clipped - 5 lines]

and files names cannot have special characters, such as the asterisk, in
them.

 

[gmazza via AccessMonster.com]

I don't have that extension, I have & ".txt"

Yes, I found all the .csv files that I needed to import.
Once I manipulate the data I need to export it out to a .txt file which is
when it gives me the error.
I just don't understand why I would get an error saying the file can't be
found, I don't need anything found for an export, I give the name and
requirements and it should export accordingly.

This is my 2 lines of code and it is failing on the 2nd line:

strFile = DLookup("Directory450", "ExportDirectory") & "SCMHeader" & Format
(Sequence, "0000000") & ".txt"

DoCmd.TransferText acExportDelim, "455ExportSpecs", "SCMHeader", strFile,
True

The error message implies that you've got

& "#txt"

not

& ".txt"

I'm confused, though. I showed you how to find all the .csv files starting
with 455 in a particular folder. Why are you using an extension of txt?

Thanks again Douglas, works perfect! Now the only problem is my export
that I

[quoted text clipped - 42 lines]

 

[Douglas J. Steele]

What's in strFile when the TransferText command is running?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I don't have that extension, I have & ".txt"

Yes, I found all the .csv files that I needed to import.
Once I manipulate the data I need to export it out to a .txt file which is
when it gives me the error.
I just don't understand why I would get an error saying the file can't be
found, I don't need anything found for an export, I give the name and
requirements and it should export accordingly.

This is my 2 lines of code and it is failing on the 2nd line:

strFile = DLookup("Directory450", "ExportDirectory") & "SCMHeader" &
Format
(Sequence, "0000000") & ".txt"

DoCmd.TransferText acExportDelim, "455ExportSpecs", "SCMHeader", strFile,
True

The error message implies that you've got

& "#txt"

not

& ".txt"

I'm confused, though. I showed you how to find all the .csv files starting
with 455 in a particular folder. Why are you using an extension of txt?

Thanks again Douglas, works perfect! Now the only problem is my export
that I

[quoted text clipped - 42 lines]

and files names cannot have special characters, such as the asterisk,
in
them.

 

[gmazza via AccessMonster.com]

strFile = "P:\Nav\SCMHeader0000001.txt"

What's in strFile when the TransferText command is running?

I don't have that extension, I have & ".txt"

[quoted text clipped - 31 lines]

 

[Douglas J. Steele]

Sorry, nothing comes to mind.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

strFile = "P:\Nav\SCMHeader0000001.txt"

What's in strFile when the TransferText command is running?

I don't have that extension, I have & ".txt"

[quoted text clipped - 31 lines]

in
them.

 

[gmazza via AccessMonster.com]

Its weird because when I take out the specs and just leave a comma like this:
DoCmd.TransferText acExportDelim, , "SCMHeader", strFile

then the Export works fine, but every field in the .txt file has quotes
around it. Only way to get rid of the quotes is to use specs no?
So when I remove the specs the Export doesn't complain about a file not
exisitng, but now I have no specs and need to get rid of the quotes. Any
thoughts on that?

Sorry, nothing comes to mind.

strFile = "P:\Nav\SCMHeader0000001.txt"

[quoted text clipped - 5 lines]