Help: Can open a spreadsheet in a folder which contains spaces in the folder name!

[1tsols]

Hello, I am new to this forum, so hello to everyone, nice to be here!

I have a project that I am developing in Visual Basic 6 and part of the project requires opening, modifying and close a excel spreadsheet. I have the code and in general works fine and without problems.

However one problem has arisen, when I try and open a excel spreadsheet that is in a folder that contains a space in the folder name, it won't open the spreadsheet. I have included the code below

Please Login or Register  to view this content.

I have tried to enclosed file name in quotes and that doesn't work

[1tsols]

Has anyone suggestions on how to get round this?

[Dave Peterson]

And you're sure you're pointing to the whole path with sExcelFile????

I've never seen this when opening a file via code, but there are times that this
happens when double clicking on a file in windows explorer that this happens.

There are two standard suggestions to correct this...

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to excel's
factory defaults.

=======
But man, oh, man, I be very surprised if that were your problem. I'm suspecting
that sExcelFile isn't what you believe it to be.



1tsols wrote:
>
> Hello, I am new to this forum, so hello to everyone, nice to be here!
>
> I have a project that I am developing in Visual Basic 6 and part of the
> project requires opening, modifying and close a excel spreadsheet. I
> have the code and in general works fine and without problems.
>
> However one problem has arisen, when I try and open a excel spreadsheet
> that is in a folder that contains a space in the folder name, it won't
> open the spreadsheet. I have included the code below
>
> Code:
> --------------------
> Dim oExcel As New Excel.Application
> Dim oWorkBook As Excel.Workbook
>
> ' before we can do anything, we need to check the excel file exists
> If IsFile(sExcelFile) Then
> ' excel file exists, proceed and open it
> oExcel.Workbooks.Open FileName:=sExcelFile
>
> ' do what i have to do.......
>
> oExcel.ActiveWorkbook.Save
> oExcel.Quit
> End If
> --------------------
>
> I have tried to enclosed file name in quotes and that doesn't work
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976

--

Dave Peterson

[Tim Williams]

What is "IsFile" ?

--
Tim Williams
Palo Alto, CA


"1tsols" <[email protected]> wrote in message
news:[email protected]...
>
> Hello, I am new to this forum, so hello to everyone, nice to be here!
>
> I have a project that I am developing in Visual Basic 6 and part of the
> project requires opening, modifying and close a excel spreadsheet. I
> have the code and in general works fine and without problems.
>
> However one problem has arisen, when I try and open a excel spreadsheet
> that is in a folder that contains a space in the folder name, it won't
> open the spreadsheet. I have included the code below
>
>
> Code:
> --------------------
> Dim oExcel As New Excel.Application
> Dim oWorkBook As Excel.Workbook
>
> ' before we can do anything, we need to check the excel file exists
> If IsFile(sExcelFile) Then
> ' excel file exists, proceed and open it
> oExcel.Workbooks.Open FileName:=sExcelFile
>
> ' do what i have to do.......
>
> oExcel.ActiveWorkbook.Save
> oExcel.Quit
> End If
> --------------------
>
>
> I have tried to enclosed file name in quotes and that doesn't work
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

[1tsols]

What is "IsFile" ?

--
Tim Williams
Palo Alto, CA


"1tsols" <[email protected]> wrote in message
news:[email protected]...
>
> Hello, I am new to this forum, so hello to everyone, nice to be here!
>
> I have a project that I am developing in Visual Basic 6 and part of the
> project requires opening, modifying and close a excel spreadsheet. I
> have the code and in general works fine and without problems.
>
> However one problem has arisen, when I try and open a excel spreadsheet
> that is in a folder that contains a space in the folder name, it won't
> open the spreadsheet. I have included the code below
>
>
> Code:
> --------------------
> Dim oExcel As New Excel.Application
> Dim oWorkBook As Excel.Workbook
>
> ' before we can do anything, we need to check the excel file exists
> If IsFile(sExcelFile) Then
> ' excel file exists, proceed and open it
> oExcel.Workbooks.Open FileName:=sExcelFile
>
> ' do what i have to do.......
>
> oExcel.ActiveWorkbook.Save
> oExcel.Quit
> End If
> --------------------
>
>
> I have tried to enclosed file name in quotes and that doesn't work
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

IsFile() is a generic routine to check that the file exists before attempting to open the file with excel.

To Dave:
I do pass the full path and filename to excel eg. "C:\Documents and Settings\JHP\My Documents\excel.xls".

I will try your suggestions tomorrow when I'm back at work.

Thanks

[Tim Williams]

So what happens when thev file doesn't open?
Any error?

Tim

"1tsols" <[email protected]> wrote in message
news:[email protected]...
>
>
> IsFile() is a generic routine to check that the file exists before
> attempting to open the file with excel.
>
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

[1tsols]

So what happens when thev file doesn't open?
Any error?

Tim

"1tsols" <[email protected]> wrote in message
news:[email protected]...
>
>
> IsFile() is a generic routine to check that the file exists before
> attempting to open the file with excel.
>
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

If I enclose the path & filename in quotes Excel says it can't find the file and to ensure that the file exists and the spelling is correct.

If I don't enclose the path & filename in quotes Excel then splits the path & filename where there are spaces and then complains it can't find the file(s).

For example, C:\Documents and Settings\JHP\My Documents\excel.xls

Without quotes I get the following messages:
'C:\Documents.xls' could not be found
'and.xls' could not be found
'Settings\JHP\My.xls' could not be found
'Documents\excel.xls' cound not be found

In quotes I get:
Run-time Error 1004: '"C:\Documents and Settings\JHP\My Documents\excel.xls"' can not be found. check spelling and verify the location.

The unregserver / regserver commands and the "Ignore other applications" options doesn't solve this.

Hope this helps.

[NickHK]

This works fine, so spaces in the file path is not your problem:

Dim strTemp As String
strTemp = "C:\Documents and Settings\Nick\Desktop\Book5.xls"
Workbooks.Open (strTemp)

Is this on an English language OS ?

NickHK

"1tsols" <[email protected]> wrote in
message news:[email protected]...
>
> Tim Williams Wrote:
> > So what happens when thev file doesn't open?
> > Any error?
> >
> > Tim
> >
> > "1tsols" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > >
> > > IsFile() is a generic routine to check that the file exists before
> > > attempting to open the file with excel.
> > >
> > >
> > >
> > > --
> > > 1tsols
> > >
> > ------------------------------------------------------------------------
> > > 1tsols's Profile:
> > http://www.excelforum.com/member.php...o&userid=36430
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=561976
> > >
>
> If I enclose the path & filename in quotes Excel says it can't find the
> file and to ensure that the file exists and the spelling is correct.
>
> If I don't enclose the path & filename in quotes Excel then splits the
> path & filename where there are spaces and then complains it can't find
> the file(s).
>
> For example, C:\Documents and Settings\JHP\My Documents\excel.xls
>
> Without quotes I get the following messages:
> 'C:\Documents.xls' could not be found
> 'and.xls' could not be found
> 'Settings\JHP\My.xls' could not be found
> 'Documents\excel.xls' cound not be found
>
> In quotes I get:
> Run-time Error 1004: '"C:\Documents and Settings\JHP\My
> Documents\excel.xls"' can not be found. check spelling and verify the
> location.
>
> The unregserver / regserver commands and the "Ignore other
> applications" options doesn't solve this.
>
> Hope this helps.
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile:
http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

[1tsols]

This works fine, so spaces in the file path is not your problem:

Dim strTemp As String
strTemp = "C:\Documents and Settings\Nick\Desktop\Book5.xls"
Workbooks.Open (strTemp)

Is this on an English language OS ?

NickHK

Can you please quantifty your statement that the spaces is not my problem?

If I open a Excel spreadsheet in any other folder that doesn't contain spaces in the path it works fine, soon as I try and open a Excel spreadsheet in a folder which does have spaces in the folder name / path then it errors!

The system this is used on is Windows XP Pro ENGLISH with Office 2000 installed.

Your code example is basically what I have already so there is no difference in that aspect.

[Tim Williams]

I think you need to show your complete code.

Tim

--
Tim Williams
Palo Alto, CA


"1tsols" <[email protected]> wrote in message
news:[email protected]...
>
> NickHK Wrote:
> > This works fine, so spaces in the file path is not your problem:
> >
> > Dim strTemp As String
> > strTemp = "C:\Documents and Settings\Nick\Desktop\Book5.xls"
> > Workbooks.Open (strTemp)
> >
> > Is this on an English language OS ?
> >
> > NickHK
>
>
> Can you please quantifty your statement that the spaces is not my
> problem?
>
> If I open a Excel spreadsheet in any other folder that doesn't contain
> spaces in the path it works fine, soon as I try and open a Excel
> spreadsheet in a folder which does have spaces in the folder name /
> path then it errors!
>
> The system this is used on is Windows XP Pro ENGLISH with Office 2000
> installed.
>
> You code example is basically what I have already so there is no
> difference in that aspect.
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

[NickHK]

Excel can happily open files from paths with spaces, so the problem is not
with Excel as such.
You are automating Excel from which environment ?
Add a line "Debug.Print sExcelFile". Copy the output and paste it into the
address bar of Explorer. Do your file open ?

NickHK

"1tsols" <[email protected]> wrote in
message news:[email protected]...
>
> NickHK Wrote:
> > This works fine, so spaces in the file path is not your problem:
> >
> > Dim strTemp As String
> > strTemp = "C:\Documents and Settings\Nick\Desktop\Book5.xls"
> > Workbooks.Open (strTemp)
> >
> > Is this on an English language OS ?
> >
> > NickHK
>
>
> Can you please quantifty your statement that the spaces is not my
> problem?
>
> If I open a Excel spreadsheet in any other folder that doesn't contain
> spaces in the path it works fine, soon as I try and open a Excel
> spreadsheet in a folder which does have spaces in the folder name /
> path then it errors!
>
> The system this is used on is Windows XP Pro ENGLISH with Office 2000
> installed.
>
> You code example is basically what I have already so there is no
> difference in that aspect.
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile:
http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

[1tsols]

I think you need to show your complete code.

Tim

--
Tim Williams
Palo Alto, CA

All the code that matters to the problem is in the first post of this thread!

[1tsols]

Excel can happily open files from paths with spaces, so the problem is not
with Excel as such.
You are automating Excel from which environment ?
Add a line "Debug.Print sExcelFile". Copy the output and paste it into the
address bar of Explorer. Do your file open ?

NickHK

Yes this does work. The problem isn't the fact we can't open the file from anywhere, it's when we are using the Workbooks.Open method!

What we are trying to achieve is to open Execl in the background so the user can't see Excel running and make modifications to the spreadsheet and save the file and close Excel.

After we have modified the file we then, using the shell function, shell out to Excel with this file (this part works fine!)

The project is been developed in Visual Basic 6.0 and run on a XP Profressional based system with Office 2000 installed.

[1tsols]

**** bump ****

[NickHK]

As no one else can reproduce your problem, are you sure the path and
filename are correct ?
This works every time in VB6, XL2K:

Dim XLApp As Excel.Application
Set XLApp = New Excel.Application
XLApp.Workbooks.Open "C:\Documents and Settings\Nick\Desktop\Book1.xls"

NickHK

"1tsols" <[email protected]> wrote in
message news:[email protected]...
>
> **** bump ****
>
>
> --
> 1tsols
> ------------------------------------------------------------------------
> 1tsols's Profile:
http://www.excelforum.com/member.php...o&userid=36430
> View this thread: http://www.excelforum.com/showthread...hreadid=561976
>

[Kaak]

I got the same problem after installing excel 2007 on my machine.
After deinstalling everything was fine.

[1tsols]

Yes, I am positive that the path and filename is correct. Have tried hard coding the path and filename instead of using sExcelFile and I still have the same problem.

I only have Office 2000 installed on both the development PC and the live system at our client's office.

[1tsols]

Do I assume people have no idea of what could be causing this error?