Import file with spaces in file name

Hi and TIA! I'm trying to import files into my db. All works well if the
file name has no spaces, but if there are spaces it bombs out. Is this a
common feature or is there a way to allow my uses to import files with
spaces in the file name. Thanks for you time!

--

Reggie

On Wed, 17 Mar 2010 14:36:29 -0700, "Reggie"
wrote:

Hi and TIA! I'm trying to import files into my db. All works well if the
file name has no spaces, but if there are spaces it bombs out. Is this a
common feature or is there a way to allow my uses to import files with
spaces in the file name. Thanks for you time!

Put the filename in quotes. I presume you're using TransferText in VBA code?
If not please explain the context.
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message
...
On Wed, 17 Mar 2010 14:36:29 -0700, "Reggie"

wrote:

Hi and TIA! I'm trying to import files into my db. All works well if the
file name has no spaces, but if there are spaces it bombs out. Is this a
common feature or is there a way to allow my uses to import files with
spaces in the file name. Thanks for you time!

Put the filename in quotes. I presume you're using TransferText in VBA
code?
If not please explain the context.
--

John W. Vinson [MVP]

John, Sorry I wasn't more specific. Here's the code I am using. Thanks
once again for you time and help.

Dim strTable As String
Dim strfilter As String

strTable = "tblAMCR_Import_Temp"
strfilter = ""
strfilter = OpenFileExt(strfilter, "Excel Files(*.XLS)", "*.XLS")
If IsNull(strfilter) Then
Exit Sub
Else
DoCmd.TransferSpreadsheet acImport, , strTable, strfilter, False
End If


--

Reggie

On Thu, 18 Mar 2010 00:05:41 -0700, "Reggie"
wrote:



John, Sorry I wasn't more specific. Here's the code I am using. Thanks
once again for you time and help.

Dim strTable As String
Dim strfilter As String

strTable = "tblAMCR_Import_Temp"
strfilter = ""
strfilter = OpenFileExt(strfilter, "Excel Files(*.XLS)", "*.XLS")
If IsNull(strfilter) Then
Exit Sub
Else
DoCmd.TransferSpreadsheet acImport, , strTable, strfilter, False
End If

Now I'm really confused. There are no variable filenames here... do you mean
that it works for tblAMCR_Import_Temp but fails if the filename is

tblAMCR Import Temp.xls

or what?
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message
...
On Thu, 18 Mar 2010 00:05:41 -0700, "Reggie"

wrote:



John, Sorry I wasn't more specific. Here's the code I am using. Thanks
once again for you time and help.

Dim strTable As String
Dim strfilter As String

strTable = "tblAMCR_Import_Temp"
strfilter = ""
strfilter = OpenFileExt(strfilter, "Excel Files(*.XLS)", "*.XLS")
If IsNull(strfilter) Then
Exit Sub
Else
DoCmd.TransferSpreadsheet acImport, , strTable, strfilter, False
End If

Now I'm really confused. There are no variable filenames here... do you
mean
that it works for tblAMCR_Import_Temp but fails if the filename is

tblAMCR Import Temp.xls

or what?
--

John W. Vinson [MVP]

No. The open file dialog opens (strfilter = OpenFileExt(strfilter, "Excel
Files(*.XLS)", "*.XLS")). The user selects the file (ex. C:\MyFile or
whatever) and this is placed into strfilter(variable file name).
tblAMCR_Import_Temp is the table I am transfering the excel spreadsheet
into. It works if strfilter = C:\MyFile but not if C:\My File.

--

Reggie

On Thu, 18 Mar 2010 09:06:51 -0700, "Reggie"
wrote:

No. The open file dialog opens (strfilter = OpenFileExt(strfilter, "Excel
Files(*.XLS)", "*.XLS")). The user selects the file (ex. C:\MyFile or
whatever) and this is placed into strfilter(variable file name).
tblAMCR_Import_Temp is the table I am transfering the excel spreadsheet
into. It works if strfilter = C:\MyFile but not if C:\My File.

Ok. Sorry, brainfade there!

Try surrounding the filename with quotemarks:

Const Q = """"
strfilter = Q & OpenFileExt(strfilter, "Excel Files(*.XLS)", "*.XLS") & Q

so that it sees "C:\My File" with the quotes.
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message
...
On Thu, 18 Mar 2010 09:06:51 -0700, "Reggie"

wrote:

No. The open file dialog opens (strfilter = OpenFileExt(strfilter, "Excel
Files(*.XLS)", "*.XLS")). The user selects the file (ex. C:\MyFile or
whatever) and this is placed into strfilter(variable file name).
tblAMCR_Import_Temp is the table I am transfering the excel spreadsheet
into. It works if strfilter = C:\MyFile but not if C:\My File.

Ok. Sorry, brainfade there!

Try surrounding the filename with quotemarks:

Const Q = """"
strfilter = Q & OpenFileExt(strfilter, "Excel Files(*.XLS)", "*.XLS") &
Q

so that it sees "C:\My File" with the quotes.
--

John W. Vinson [MVP]


John it didn't like:
strfilter = Q & OpenFileExt(strfilter, "Excel Files(*.XLS)", "*.XLS") & Q

however this worked like a champ:

strfilter = OpenFileExt(cDQ & strfilter & cDQ, "Excel Files(*.XLS)",
"*.XLS")

Thanks!
--

Reggie