Spaces in folder name and file name

[Merlynsdad]

I'm defining a constant that will access a file. I've been testing it with
the following constant:

Const cstrDatabaseWB As String = "C:\Documents and Settings\5YPJB\My
Documents\Excel documents\Sherry project\real1.xls"

That works fine. Now I'm working with the real file, named by someone else,
and I've got the constant defined as

Const cstrDatabaseWB As String = "G:\WI\ACS\ACS QA\FY10 CQRS and Local
Reviews (version 1).xls"

There is a space in the folder name "ACS AQ" and multiple spaces in the
filename. I'm getting a Run time Error 1004 Application/Object Defined Error
when the code tries to execute the following line:

Application.Workbooks.Open (cstrDatabaseWB)

I tried using single quotes inside the double quotes around the file name
but that didn't help. How do I need to modify this code to open this file?

Any help will be greatly appreciated.

 

[Dave Peterson]

The code isn't the problem.

You've got a mismatch between your spelling of the drive/path/filename in your
code and the actual name of the file.

 

[OssieMac]

I wonder if there is an additional space (or something) that you are not
seeing in the path and filename.

Using the workbook with your code, in VBA open the immediate window (menu
item View->Immediate Window or Ctrl + G)

Open the required workbook and ensure that it is the active workbook.

Select the VBA window and in the Immediate Window insert the following and
then press Enter.

Debug.Print ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

Copy the returned result and paste it into your code between the double
quotes in lieu of the path and name you have used.

 

[Dave Peterson]

Just to save you some checking.

Open that file manually.

Then make sure it's the activeworkbook.
Open the VBE (alt-f11 is one way)
show the immediate window (ctrl-g)
and type this:
?activeworkbook.fullname
(and hit enter)

You'll be able to copy|paste from that immediate window into your code.