"Select " in filename on DoCmd.TransferText

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
I have code to import a .txt file into a table. I'm running into an issue where the filename begins with "Select " (note the space after Select). If there is no space after "Select" the code runs properly. This is clearly an issue with how the DoCmd.TransferText is processed and not the code.

Code:
 DoCmd.TransferText acImportFixed, msSpec, msImportTable, msFileName, False
Filename:
"Select Test.txt"

Error:
Syntax error (missing operator) in query expression 'Test#.txt'.
(3075)


I can check for "Select " in the filename and change it prior to import, but I'm wondering if there is a solution with double quotes or single quotes to get the function to read the inputs correctly. I've tried single quotes and a couple variations of double and single quotes without luck.

Anyone aware of a workaround other than checking the filename before import?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
I am pretty sure it is mistaking it for SQL Code, which often starts out "Select ...".
So I am going to say that you cannot have file names that start with the word "Select" followed by a space.
If there was no space there, or something else like an underscore, it works fine.

I am not sure where these files are coming from, but if you have any ability to tell them not to use spaces in the file names, it would solve the problem (best practice says not to do that anyway).
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Completely agree with you about "best practices". It's been discussed, but unfortunately it's out of my control. Also agree it's the VBA function processing. Thought I'd ask. If anyone else has suggestions I'm open, thanks Joe4.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
Try enclosing the filename in square brackets.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Thanks for the suggestion Norie, doesn't seem to be working.

Brackets outside quotes:
["C:\Temp\Select Test.txt"]

"Run-time error '2465':
Microsoft Access can't find the field '|1' referred to in your expression"


Brackets inside quotes:
"[C:\Temp\Select Test.txt]"

"You cannot import this file. (31519)"



Also tried with single quotes and without quotes are received the same errors.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
Did you try putting the brackets around the filename, not the path and filename?
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Sorry, wasn't clear. I was testing without the variable, instead I used a text listed above. It didn't work with the variable either.

Code:
DoCmd.TransferText acImportFixed, msSpec, msImportTable, [msFileName], False
This code caused the following error.

"Syntax error in date in query expression 'Test#txt'. (3075)
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,626
Office Version
2013
Platform
Windows
It looks like you will have to avoid this unfortunately named file and give it a different name, at least temporarily. Its a weird bug I guess.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
Still not quite what I mean.:)

Try this.
Code:
DoCmd.TransferText acImportFixed, msSpec, msImportTable, "[" & msFileName & "]", False
By the way, if you are using a Schema.ini file you can specify the filename in that.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Still not quite what I mean.:)

Try this.
Code:
DoCmd.TransferText acImportFixed, msSpec, msImportTable, "[" & msFileName & "]", False
By the way, if you are using a Schema.ini file you can specify the filename in that.

Thanks Norie, this format would have been included in the earlier testing, but I've tested again in variable form with the same error.

"You cannot import this file. (31519)"


Unfortunately, it's a multiuser application and could be any file in the acceptable format. Many new files are generated every month, I'm guessing 2,000 last year would have been imported by the users. All on different servers with different file names. In this particular case, Schema.ini is not likely a workable option.

Thanks for the suggestions.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,232
Messages
5,443,263
Members
405,220
Latest member
gtgaabaron

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top