Macro in Access to format and load data to access

[101287]

I would like to generate some access code that will be an access in Macro that will perform the following capabilities:
(1) The access macro will open an excel spreadsheet.
(2) The excel spreadsheet will be formatted to generate a new worksheet.
(3) This worksheet will be access from access to load a tbl.

Is there any code and/or access article to show and explain how to get access from MS Access to perform some of the capability explained. Or code that shows the connectivity from MS access to Excel.

Your guidance and suggestions will be appreciated.

Luis

 

[PHV]

Have a look at the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[101287]

PHV - Did my research and I'm getting the following error;
"This operation requires an Open Database".

I thought that the code below will provide the connectivity required to execute them. Do you have suggestions?

Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = _
"F:\EVMS\ITPlanningDB.mdb"
.Open
End With
PathName = "F:\EVMS\TestingMacroFINAL PRIORITIZATION 2008628830v2.xls"
SheetName = Sheets(1).Name
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=8, _
TableName:="LuisTest", _
Filename:=PathName, _
HasFieldNames:=True

Your guidance will be appreciated.

Luis

 

[PHV]

Isn't your code Access VBA ?
If so, I wonder why you don't have a current database.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

BTW, you've already have an answer to your question in your own thread181-1340926