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


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

 

[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.

 

[Tim Williams]

What is "IsFile" ?

 

[1tsols]

What is "IsFile" ?

--
Tim Williams
Palo Alto, CA


message

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

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

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

Thank

 

[Tim Williams]

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

Tim

 

[1tsols]

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

Tim

message

If I enclose the path & filename in quotes Excel says it can't find th
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 th
path & filename where there are spaces and then complains it can't fin
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\M
Documents\excel.xls"' can not be found. check spelling and verify th
location.

The unregserver / regserver commands and the "Ignore othe
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]

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 m
problem?

If I open a Excel spreadsheet in any other folder that doesn't contai
spaces in the path it works fine, soon as I try and open a Exce
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 200
installed.

You code example is basically what I have already so there is n
difference in that aspect

 

[Tim Williams]

I think you need to show your complete code.

Tim

 

[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]

I think you need to show your complete code.

Tim

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!)

 

[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

 

[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 trie
hard coding the path and filename instead of using sExcelFile and
still have the same problem.

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

 

[1tsols]

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