TransferSpreadsheet question

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

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
 
Ken Warthen said:
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?
Click to expand...


Try acSpreadsheetTypeExcel12 for the SpreadsheetType. If that isn't
defined, use the numeric value 9.
 
hello im new here just having a bit of difficulties trying to locate the new thread link can you assist me
 
Dirk Goldgar said:
"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
Click to expand...

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
Click to expand...
 
Back
Top