"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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. 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
76,281
Office Version
  1. 365
Platform
  1. Windows
Try enclosing the filename in square brackets.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289

ADVERTISEMENT

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
76,281
Office Version
  1. 365
Platform
  1. Windows
Did you try putting the brackets around the filename, not the path and filename?
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. 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
76,281
Office Version
  1. 365
Platform
  1. 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,129,573
Messages
5,637,131
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top