Excel range import issue

[Wile_E]

Hi,

we get an excel spreadsheet from our suppliers for the silicon wafers they supply us, it contains debug and failure info, and i need to import some of it into our database. my issue is the worksheet names are #01 - #25 and i cant get access to see these worksheets

i use
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, fileName, False, "#01!B3:I11"

(fileName is a string variable set from a file picker)

but it errors out saying that '.01!B3:I11' cant be found.

as a test i changed the name of one worksheet to S01 and that works fine.
there is no option to have the supplier change the worksheet name (its auto generated) and i would rather not have to change 25 worksheet names by hand for every order. is there a way to stop access changing the # to a .?

hope you guys can help
thanks

 

[vba_php]

i use
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, fileName, False, "#01!B3:I11"

(fileName is a string variable set from a file picker)

but it errors out saying that '.01!B3:I11' cant be found.

as a test i changed the name of one worksheet to S01 and that works fine.

I think you've got some options here Wile. First of all, this:

.01!B3:I11

makes no sense whatsoever. access will surely not recognize that as anything legitimate. if you're trying to import RANGES on every one of your worksheets instead of the ENTIRE content on each worksheet, then you should look at these results:

https://www.google.com/search?q=ms+access+import+excel+range

the other option you have if that fails is to write a vba script that you can run in excel before you attempt to import your excel content into the access db whereby it automatically renames all of your worksheets that access will recognize, which you have already done via your test of the worksheet name "S01"

 

[vba_php]

Wile,

I just tried to run a test on what you're trying to do, and are you aware that the "auto generation" of the field names that access attempts on the operation might hinder your effort? my method was just like yours, and even though FALSE is in the "HAS FIELD NAMES" argument, stupid access still tried to map the range to my TEST table and reference field names automatically that did not exist, hence the error you see in the attached image.

 

[Wile_E]

thanks for the reply

i have found that it works flawlessly if i change the worksheet name from '#01' to 'S01' so the issue is definitely with the '#'. it seems that access is treating this as a special character and not just a sting. i have tried using a string variable to force it to be a string but this does not help

strangely if you use the msgbox function to see the variable it reads correct but still fails in the docmd

would trying to automate the name change not run into the same issue?

thanks
Kevin

 

[vba_php]

would trying to automate the name change not run into the same issue?

automating the name change works fine Wile, because you know you've already run the test on it. so you can simple do this all from access vba if you want to. you are already using the transferspreadsheet method in vba so you obviously know how to code. use an excel object instance to open it in INVISIBLE mode, change the sheet names via a worksheet loop, close the instance and run your transfer op. it's that simple. so to start, you would write:

dim xl as excel.application
set xl = new excel.application

dim ws as xl.worksheet
set ws = xl.openworkbook("file name here")

'loop thru sheets here, rename each one
'save
ws.save

KILL excel instance
'xl.quit

or, if you want to watch it happen, add this before "set ws...":

xl.visible = true

that code might not work perfectly, but you know how to change it.

 

[Gasman]

Hi,

we get an excel spreadsheet from our suppliers for the silicon wafers they supply us, it contains debug and failure info, and i need to import some of it into our database. my issue is the worksheet names are #01 - #25 and i cant get access to see these worksheets

i use
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, fileName, False, "#01!B3:I11"

(fileName is a string variable set from a file picker)

but it errors out saying that '.01!B3:I11' cant be found.

as a test i changed the name of one worksheet to S01 and that works fine.
there is no option to have the supplier change the worksheet name (its auto generated) and i would rather not have to change 25 worksheet names by hand for every order. is there a way to stop access changing the # to a .?

hope you guys can help
thanks

I'd just open the file in VBA and rename all the sheets, close it, then run your code?

 

[Wile_E]

that's what i'm doing and it seems to be working

thanks guys