H
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
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