Run Excel VBA code inside Access

[hansjhamm]

I can get the code to open the correct wkbk, but cannot get it to
execute the excel, what I call "cleanup" portion. The replace
section....It just does nothing, anybody know why?

Private Sub Command4_Click()
DoCmd.RunSQL "Delete * From CAEmployees"
Dim objXL As Object
Dim Sheets As Object
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\Documents and Settings\Administrator\My
Documents\N Conner Employee List.XLS"
NConnerEmployeeList.xls.Activate
Sheets("emplistwithbydiv").Select
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("T2:T" & LastRow)
Cells.Replace What:="F", Replacement:=""
Cells.Replace What:="S", Replacement:=""
Cells.Replace What:="P", Replacement:=""
End With
With .Range("R2:W" & LastRow, "O2" & LastRow, "N2" & LastRow,
"A2:E" & LastRow)
.NumberFormat = "0"
End With
End With
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"CAEmployees", "N Conner Employee List.xls", -1
DoCmd.RunSQL "update CAEmployees set[Name]=[Nick Name]&' '&[Last Name]"
End Sub

THKS,

Hans

 

[Jim Cone]

Hans,
This ought to get you closer. I am not sure if Access is going to know
what to do with "What" and "Replacement". You may have to use
the values without the argument names...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Private Sub Command4_Click()
DoCmd.RunSQL "Delete * From CAEmployees"
Dim objXL As Object
Dim objWB As Object
Dim objSht As Object
Dim LastRow As Long
'On Error Resume Next
Set objXL = CreateObject("Excel.Application")
objXL.Application.Visible = True
Set objWB = objXL.Application.Workbooks.Open("C:\Documents and " & _
"Settings\Administrator\My Documents\N Conner Employee List.XLS")
Set objSht = objWB.Sheets("emplistwithbydiv")
LastRow = objSht.Cells(objSht.Rows.Count, "A").End(xlUp).Row
objSht.Range("T2:T" & LastRow).Replace What:="F", Replacement:=""
objSht.Range("T2:T" & LastRow).Replace What:="S", Replacement:=""
objSht.Range("T2:T" & LastRow).Replace What:="P", Replacement:=""
objSht.Range("R2:W" & LastRow, "O2" & LastRow, "N2" & LastRow, "A2:E" & _
LastRow).NumberFormat = "0"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CAEmployees", "N Conner Employee List.xls", -1
DoCmd.RunSQL "update CAEmployees set[Name]=[Nick Name]&' '&[Last Name]"
End Sub
'-------------



<[email protected]>
wrote in message
I can get the code to open the correct wkbk, but cannot get it to
execute the excel, what I call "cleanup" portion. The replace
section....It just does nothing, anybody know why?

Private Sub Command4_Click()
DoCmd.RunSQL "Delete * From CAEmployees"
Dim objXL As Object
Dim Sheets As Object
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\Documents and Settings\Administrator\My
Documents\N Conner Employee List.XLS"
NConnerEmployeeList.xls.Activate
Sheets("emplistwithbydiv").Select
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("T2:T" & LastRow)
Cells.Replace What:="F", Replacement:=""
Cells.Replace What:="S", Replacement:=""
Cells.Replace What:="P", Replacement:=""
End With
With .Range("R2:W" & LastRow, "O2" & LastRow, "N2" & LastRow,
"A2:E" & LastRow)
.NumberFormat = "0"
End With
End With
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"CAEmployees", "N Conner Employee List.xls", -1
DoCmd.RunSQL "update CAEmployees set[Name]=[Nick Name]&' '&[Last Name]"
End Sub
THKS,
Hans