TransferSpreadsheet question

[Ken Warthen]

I'm using the TransferSpreadsheet method to import an Excel spreadsheet into
an Access 2007 table. I've set the SpreadsheetType parameter to
acSpreadsheetTypeExcel9. This works when the spreadsheet is a .xls file, but
the spreadsheet I'm importing is a .xlsm file. To make the code work I open
the .xlsm file in Excel and then do a SaveAs where I can change the file type
to .xls. This is not really an acceptable solution for end users of the
application. Is there a way to import spreadsheets in the .xlsm format?

Ken

 

[Dirk Goldgar]

I'm using the TransferSpreadsheet method to import an Excel spreadsheet
into
an Access 2007 table. I've set the SpreadsheetType parameter to
acSpreadsheetTypeExcel9. This works when the spreadsheet is a .xls file,
but
the spreadsheet I'm importing is a .xlsm file. To make the code work I
open
the .xlsm file in Excel and then do a SaveAs where I can change the file
type
to .xls. This is not really an acceptable solution for end users of the
application. Is there a way to import spreadsheets in the .xlsm format?

Try acSpreadsheetTypeExcel12 for the SpreadsheetType. If that isn't
defined, use the numeric value 9.

 

[Ken Warthen]

Dirk,

Thanks for the help. That worked fine, of course.

Ken

 

[Nkule]

hello im new here just having a bit of difficulties trying to locate the new thread link can you assist me

 

[Carl Thompson]

"Ken Warthen" <[email protected]> wrote in message
news:[email protected]...
> I'm using the TransferSpreadsheet method to import an Excel spreadsheet
> into
> an Access 2007 table. I've set the SpreadsheetType parameter to
> acSpreadsheetTypeExcel9. This works when the spreadsheet is a .xls file,
> but
> the spreadsheet I'm importing is a .xlsm file. To make the code work I
> open
> the .xlsm file in Excel and then do a SaveAs where I can change the file
> type
> to .xls. This is not really an acceptable solution for end users of the
> application. Is there a way to import spreadsheets in the .xlsm format?


Try acSpreadsheetTypeExcel12 for the SpreadsheetType. If that isn't
defined, use the numeric value 9.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Hi,

I am attempting to import a .xlsm spreadsheet into access by using vb in Excel to run an existing macro in MS Access 2007. When attempting to import a .xlsm file I receive the following message:

"External Table is not in the expected format".

My vb code to open the MS Access Database and run the MS Access macro is as follows:

Dim oMSAccess As Object
Dim openapp As Boolean
Dim Path As String


Path = "\\........................................mdb"

openapp = False
On Error Resume Next
Set oMSAccess = GetObject(, "Access.Application")

oMSAccess.DoCmd.RunMacro "mcrImportInitialEstimate"
oMSAccess.Visible = True

If oMSAccess Is Nothing Then
Set oMSAccess = CreateObject("Access.Application")
oMSAccess.Visible = True

oMSAccess.OpenCurrentDatabase (Path)
oMSAccess.DoCmd.RunMacro "mcrImportInitialEstimate"

openapp = True
End If

End Sub

In your previous reply you suggest determining the excel spreadsheet as acSpreadsheetTypeExcel12. Should I enter some line of code within my spreadsheet which saves the spreadsheet as this format prior to attempting to run the Access Macro?

Or

Is there a way of importing a .xlsm file into MS Access without defining the SpreadsheetType? Is there a required add-in of sorts?

Thanks in advance.

Carl