Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
Has anyone suggestions on how to get round this -- 1tsol ----------------------------------------------------------------------- 1tsols's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56197 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces inthe folder name!
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can open a spreadsheet in a folder which contains spaces in the folder name!
What is "IsFile" ?
-- Tim Williams Palo Alto, CA "1tsols" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
Tim Williams Wrote: What is "IsFile" ? -- Tim Williams Palo Alto, CA "1tsols" wrote i message ... Hello, I am new to this forum, so hello to everyone, nice to b here! I have a project that I am developing in Visual Basic 6 and part o 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 exce spreadsheet that is in a folder that contains a space in the folder name, i 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 fil 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 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 -- 1tsol ----------------------------------------------------------------------- 1tsols's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56197 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
So what happens when thev file doesn't open?
Any error? Tim "1tsols" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
Tim Williams Wrote: So what happens when thev file doesn't open? Any error? Tim "1tsols" wrote i message ... 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 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 -- 1tsol ----------------------------------------------------------------------- 1tsols's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56197 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
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" wrote in message ... Tim Williams Wrote: So what happens when thev file doesn't open? Any error? Tim "1tsols" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
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 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 -- 1tsol ----------------------------------------------------------------------- 1tsols's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56197 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
I think you need to show your complete code.
Tim -- Tim Williams Palo Alto, CA "1tsols" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
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" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
Tim Williams Wrote: 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 ------------------------------------------------------------------------ 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430 View this thread: http://www.excelforum.com/showthread...hreadid=561976 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
NickHK Wrote: 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 ------------------------------------------------------------------------ 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430 View this thread: http://www.excelforum.com/showthread...hreadid=561976 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
**** bump *** -- 1tsol ----------------------------------------------------------------------- 1tsols's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56197 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
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" wrote in message ... **** bump **** -- 1tsols ------------------------------------------------------------------------ 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430 View this thread: http://www.excelforum.com/showthread...hreadid=561976 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
I got the same problem after installing excel 2007 on my machine. After deinstalling everything was fine. -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=561976 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
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 -- 1tsol ----------------------------------------------------------------------- 1tsols's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56197 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Can open a spreadsheet in a folder which contains spaces in the folder name!
Do I assume people have no idea of what could be causing this error? -- 1tsols ------------------------------------------------------------------------ 1tsols's Profile: http://www.excelforum.com/member.php...o&userid=36430 View this thread: http://www.excelforum.com/showthread...hreadid=561976 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
How to decide folder-depth or How to select more folders/subfolders (folder-tree) ? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
open file from folder save in new folder | Excel Programming |