Setting Excel version in Access Basic

[waynemb]

If I know the Excel version is 9 how can I get Basic to use that in a
TransferSpreadsheet call?

This doesn't work:
dim ExcelVersion as AcSpreadSheetType
ExcelVersion =9
DoCmd.TransferSpreadsheet acImport, ExcelVersion, TheTableName, ....

Nor does this:
dim ExcelVersion as string
ExcelVersion = "AcSpreadSheetType9"
DoCmd.TransferSpreadsheet acImport, ExcelVersion, TheTableName, ....

Thanks,

 

[Van T. Dinh]

I think it is simpler to use the in-built symbolic constant
acSpreadsheetTypeExcel9.

Is there any reason you want to use an intermeadiate variable?

 

[waynemb]

Yeah, because I make a lot of calls to Transferspreadsheet, and I hate to
have to go through the whole rigamarole each time; to go through all the
options of what the call has to be if ExcelVersion is 11, 10, 9...

Case ExcelVersion=11
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel11, TheTableName,...

CaseExcelVersion=10
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel10, TheTableName,...

etc. It would be so much easier to just determine the parameter once and
then pass it to the TransferSpreadsheet call each time I use it, instead
having to replicate a couple dozen lines of code each I make that call.

Any suggestions? Thanks.

 

[Van T. Dinh]

Create a Public UDF using your ExcelVersion as input and returning the value
of the appropriate symbolic constant. You can then use your UDF in the
TransferSpreadsheet in place of the symbolic constant.

 

[Norbert Bous via AccessMonster.com]

If I know the Excel version is 9 how can I get Basic to use that in a
TransferSpreadsheet call?

This doesn't work:
dim ExcelVersion as AcSpreadSheetType
ExcelVersion =9
DoCmd.TransferSpreadsheet acImport, ExcelVersion, TheTableName, ....

Nor does this:
dim ExcelVersion as string
ExcelVersion = "AcSpreadSheetType9"
DoCmd.TransferSpreadsheet acImport, ExcelVersion, TheTableName, ....

Thanks,

The greatest possible value of AcSpreadSheetType amounts to 8, which
corresponds to the constants of acSpreadsheetTypeExcel8,
acSpreadsheetTypeExcel9 and acSpreadsheetTypeExcel97.

 

[George Nicholson]

1) Per the VBE Object Browser both acSpreadSheetType8 and acSpreadSheetType9
constants are assigned a value of 8. Setting your variable to 9 in #1 might
be failing because 9 isn't an expected value for *any* AcSpreadSheetType
constant value in Access 2002/XP. Not sure if that changes for 2003, but I
tend to doubt it.

2) Using VarType on a AcSpreadSheetType variable reveals that it is a Long
Variable. This is what TransferSpreadsheet expects for that argument so the
failure of the string assignment in #2 is not all that surprising: its a
Type Mismatch.

HTH,

 

[Klatuu]

No, it has not changed. The possible values are:
acSpreadSheetType3 = 0
acSpreadSheetType4 = 6
acSpreadSheetType5 = 5
acSpreadSheetType7 = 5
acSpreadSheetType8 = 8
acSpreadSheetType9 = 8

IHMO, this idea is the height of lazy. Sure, you have to type a few more
characters, but the whole idea of constants has value. First, someone else
reading your code can understand it easily. They don't have to go look in
the Object Browser to see what you are doing. Second, it avoids possible
version problems. What if in Access 200? Microsoft decides to change all the
constant values to align with the version numbers. Now your code has to be
changed.

 

[waynemb]

I don't mind typing in the constants, I just don't want to have to type in a
select case structure with all the possible calls to Transferspreadsheet
every time I use it.

I'd like to be able to determine which constant to use (acSpreadSheetType8
for example) and then store that constant as a variable to be plugged into
Transferspreadsheet each time I call it. I made a variable of type
acSpreadSheetType, but I can't get that to work.

But I guess I'm more confused than I thought. I used a version finder
suggested elsewhere by mvp Dave Patrick, but that returns version numbers
greater than 8. Is this some different kind of version number? How can I
determine the proper version number corresponding to the acSpreadSheetTypes
you wrote about below?

(Here's Dave Patrick's version finder

Public Function GetExcelVersion()
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
Select Case WshShell.RegRead("HKCR\Excel.Application\CurVer\")
Case "Excel.Application.8"
ExcelVersionParm = 8
Case "Excel.Application.9"
ExcelVersionParm = 9
Case "Excel.Application.10"
ExcelVersionParm = 10
Case "Excel.Application.11"
ExcelVersionParm = 11
End Select

 

[waynemb]

The function I have that returns the Excel version returns it as a number.
Is there a simple way to ask Basic to return the proper version number
constant?

Thanks again.

 

[Klatuu]

I am on Office 2003, Access 2003, Excel 2003, which, I believe to be the most
current version of everything. The numbers I sent were found this way:
In your VB editor, select the Object Browser. Then, in the Library combo,
select Access, and in the search box acspreadsheettype. It shows all the
possible vaules there. Now, I will say that sometimes the info in various
parts of Microsoft products can be incomplete, but we have users on both 2000
and 2003, and useing acSpreadsheetType9 has never been a problem.

Also, you don't need to declare the variable the way you are doing it. You
can declare it as an integer or a long.
Dim intSpreadsheetVersion as Integer

What Dave Patrick's code is getting is not the same thing. That is
returning the actual version number of the application. The spreadsheet type
constant is a whole different animal.

 

[waynemb]

Is there an easy way for Basic to find out which of the acSpreadSheetType
constants it should be using with TransferSpreadsheet; something that will
return either the proper number or the actual constant?

Thanks again for your help.

 

[Klatuu]

No. If it knew, there would be no need for that argument. Now, my initial
thought was okay, use Dave Patrick's routine to determine the Excel version
and translate it into the correct spreadsheet version argument. The problem
is, it does not look at a spreadsheet, it looks at the version of Excel
installed on your computer, so that wont work. If there is a way to
determine the version of Excel a workbook was created in, you could use that
information and translate it into which argument constant to use, that would
do the trick.

You might try posting in the Excel groups to see if anyone knows if that can
be done.

 

[waynemb]

Hmmm, so unless you can get a user to find out and enter in what version of
spreadsheet he has, you can't guarantee that TransferSpreadsheet is not going
to crash, for not knowing what it's opening?

But anyway, given all that, I guess I could at lease improve my odds by
taking your suggestion, using Dave Patrick's routine to find the installed
Excel version number and assuming that a spreadsheet on that machine will
likely correspond.

But how do I find out which spreadsheet versions correspond to the various
version numbers returned by Patrick's routine? His version numbers run from
8 to 11. The Object Browser you suggested shows Excel versions running from
3 through 9 (corresponding to acspreadsheettypes 0-8).

It seems to me that the Object Browser info is showing some third type of
version number, which is not spreadsheet version or the Dave Patrick version.
Is that correct? Do you have any suggestions for how I can get Basic to
return the 3-9 version number shown in Object Browser?

Thanks again

 

[Klatuu]

I know this is a bit confusing.
Dave Patrick's routine returns the installed version of an Application. It
has nothing to do with the version a spreadsheet was created with.
Certainly, one would expect that most of the spreadsheets on a user's
computer would be created with the same version as the application version,
but that is not an absolute.
The acSpreadsheetTypeExcel constants refer to the version of Excel the
spreadsheet was created with. On import, it reads it in that format. On
export, it uses that to format the sheet it creates.
I think perhaps you don't really need to go to all that troulbe. Excel can
open older versions. How far back I can't guarantee, but I would guess it
could open any of them.
When you do a transferspreadsheet, you are creating an instance of Excel on
the machine where the Access code is running, so that will be the version of
Excel that is running. So if Excel can open most if not all older versions
of excel spreadsheets, I don't know that using anything other than
acSpreadsheetTypeExcel9 is of any value unless you are doing an export and
know that the user who will use the spreadsheet doesn't have a new version of
Excel.

 

[waynemb]

It sounds like you're saying that if the user doesn't tell you which version
of spreadsheet you are about to link to, then just use the default and hope
that it works.

But let's say that I were to use Dave Patrick's code in hopes of improving
my odds a bit, and it returns a version number of 11. How would I know what
value to plug into TransferDatabase? Is there a chart somewhere showing what
spreadsheet version number would be created by Patrick's Excel version number
11?

Thanks

 

[Klatuu]

I don't know if there is such a chart. I do know that version 11 is 2003
which would use acSpreadsheetTypeExcel9, 2002 is version 10, and 2000 is
version 9. Older than that, I don't know.
I do a lot of transfers between Access and Excel and have never had a
version problem.

 

[waynemb]

thanks

I don't know if there is such a chart. I do know that version 11 is 2003
which would use acSpreadsheetTypeExcel9, 2002 is version 10, and 2000 is
version 9. Older than that, I don't know.
I do a lot of transfers between Access and Excel and have never had a
version problem.

 

[Van T. Dinh]

I wrote previously that you could create a public UDF (User-Defined
Function) to do this).

Something like:

****Untested****
Public Function fnExcelSpreadsheetType _
(ExcelVersion As Integer) As Integer

Dim intTemp As Integer

Select Case ExcelVersion
Case 7
intTemp = acSpreadsheetTypeExcel7
Case 8
intTemp = acSpreadsheetTypeExcel8
Case 9, 10
intTemp = acSpreadsheetTypeExcel9
Case Else
...
End Select

fnExcelSpreadsheetType = intTemp

End function
***Code ends****

You can then use fnExcelSpreadsheetType(ExcelVersion) in your
TransferSpreadsheet statement.

 

[waynemb]

Well, that looks good, but the hard part here is getting Access to set
ExcelVersion for you. As I am understanding it from other folks, there is no
way to ask Access for a spreadsheet version number, and the best you can do
is maybe look at the version number of the installed Excel application and
hope that your spreadsheet was created by the installed application. But
then nobody seems to know where I can find a chart to convert the application
version into the spreadsheet version.

Don't suppose you would know where to find such a conversion chart, or how
to get Access to reveal the version number of a specific .xls file?

Thanks.

 

[Van T. Dinh]

Dave Patrick's code gives the Excel version number of the software installed
on the PC. I don't know about the Excel file-format version number.

Suggest you post in the Excel newsgroup asking on how to get the Excel
version number from an Excel workbook file.