Excel VBA code inside Access

[hansjhamm]

FYI...I also posted this on the Excel page for help
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

 

[strive4peace]

Hello Hans,

you need to preface your Excel commands with an object...

also, since you are using Late binding (Dim objXL As Object) instead of
Early binding (Dim objXL As Excel.Application), you cannot use Excel
constants

Cells.Replace What:="F", Replacement:=""
-->
objXL.ActiveSheet.Cells.Replace "F", ""

here is some "shell" code I use when I am going to write a program with
Excel automation...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Excel_Conversation()

On Error GoTo Proc_Err

Dim xlApp As Excel.Application, _
booLeaveOpen As Boolean

'if Excel is already open, use that instance
booLeaveOpen = True

'attempting to use something that is not available
'will generate an error
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo Proc_Err

'If xlApp is defined, then we
'already have a conversation
If TypeName(xlApp) = "Nothing" Then
booLeaveOpen = False
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
End If

'Do whatever you want


Proc_Exit:
On Error Resume Next

If TypeName(xlApp) <> "Nothing" Then
xlApp.ActiveWorkbook.Close False
If Not booLeaveOpen Then xlApp.Quit
Set xlApp = Nothing
End If

Exit Function

Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " Excel_Conversation"
'comment next line after debugged
Stop: Resume

Resume Proc_Exit
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~
I often use a template to make new workbooks with one sheet ("master"),
that I make copies of, fill and rename. I have found the the formatting
doesn't always stick in Excel, so this syntax has been very handy for
formatting a range...

xlApp.Range(xlSht.Cells(8, 5), xlSht.Cells(mRow + 1, 10)).NumberFormat =
"#,##0"

'~~~~~~~~~~~~~~~~~~~~~~~~
and for putting formulas into Excel instead of calculation results...

xlSht.Cells(mRow, 7).Formula = "=IF(E" & mRow & "=0,0,F" & mRow & "/E" &
mRow & ")"

pSht.Cells(pRow2, 5).Formula = "=SUM(E" & pRow1 & ":E" & pRow2 - 1 & ")"

"p" is my passed parameter notation -- to modularize the code, I often
send a recordset, an Excel object reference, possibly row numbers,
etc... to another routine to do the writing to Excel. That way, it is
easier to add a loop too

'~~~~~~~~~~~~~~~~~~~~~~~~
here's another handy tip...

to launch Excel code from Access

'this is the workbook with the code if it is somewhere else
xlApp.Workbooks.Open mPath & "PROGRAMS.XLS"

'this is the workbook to run code on, or just to open
xlApp.Workbooks.Open mExcelFile

'run Sub in Programs Workbook if applicable
xlApp.Run "PROGRAMS.XLS!ModuleName.SubName"

'~~~~~~~~~~~~~~~~~~~~~~~~
to make a new workbook based on a template...

xlApp.Workbooks.Add _
Template:= _
CurrentProject.Path _
& "\Templates\Filename.xlt"

'~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

FYI...I also posted this on the Excel page for help
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