Jet Engine Excel Error - Extra

[daint]

Sorry, forgot to add a little info...

I have have made a VB program to access both a MS Access database and a MS Excel file. However, when the sheet names on my Excel file have spaces in, my VB prog gives me a error

'the microsoft jet engine could not find the object ....'

I know I could just rename the sheet, but this would defy the whole point of my program.

I have tried using ' before and after the sheet names and the $ symbol which VB supplies in the 'Connect' drop down list.

I am using Excel 8.0 data type in VB and an Excel 2000 file, would this cause any problems?

Any help would be greatly appreciated

Thanks

Daint

 

[jofarrell]

Programmers hate spaces in file names and folder names (at least I do). Is there any chance you could even remove the spaces and use an underscore? VB will like My_File much better than My File. And playing with single and double quotes is never fun. To save you a lot of coding problems is it easy enough just rename your sheets? And for good coding practice try and avoid spaces... you and your coding language will enjoy it more. I am not sure if the DOS verison of the name will work for you. You can try and see if you dont like the renaming process ... its 6 letters a tilde (~) and then number one (1) unless you of course have more than one file that begins with the same 8 letters. Hope this helps in some way. For example Program Files would be progra~1 ... just an idea

Joanne

 

[chiph]

Try surrounding the name that's causing problems with square brackets -- [My xls that has spaces in it]

Chip H.

 

[daint]

I've tried chiph idea, but no luck, tried
['Not Approved$'], ['Not Approved'$], '[Not Approved$]', '[Not Approved$]', '[Not Approved]$'

And in response to jofarrell, part of the problem with my program is that I can't rename the worksheets. Is there a way I can get VB to do this for me??

Thanks for the help

Cheers

Daint

 

[MisterC]

OK, this may not be a perfect solution...
Why not reference the worksheets by their index? i.e. instead of 'Not Approved' you could use Sheets(2) or Sheets(3) or whatever the index of the sheet is.

 

[daint]

Good idea, but how can I go about doing this,

How do I reference a sheet by number from within a Data source in VB.

Syntax at the moment is :-
xxxxx.Data1.RecordSource = "'Not Approved$'"

What would the new syntax be like?

If this doesn't work, is there any way I can rename a worksheet from within VB?

Thanks

 

[MisterC]

OK, Now that I've taken a look at it...
I was able to connect to a worksheet with a space in its name by using (for example) 'Not Approved'$ as the recordsource. Try putting the $ outside of the single quotes. ie:

Data1.RecordSource="'Not Approved'$"
Data1.Refresh

Hope that helps...