Shouldn't the last workbook/worksheet created be the Active one?

I

Imran J Khan

I have MS Access VBA code (thanks to this forum's Jacob Skaria) that inserts
a column into a worksheet created by another Access VB module. Because the
module creates other worksheets as well, I did not want to modify it. Not the
code does evrything I want except with one thing. If the user runs the code a
second (or subsquent) time without closing the first Excel workbook, the
column is inserted into the first workbook, not the last one to be created.
How can I get the column to be inserted on in the last workbook created.
Private Sub InsertExcelCol()
Dim ExcelApp As Excel.Application
Dim ExcelWrk As Excel.Workbook
Dim ExcelWks As Excel.Worksheet

On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Debug.Print Err.description
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Error! " & Err.description
End If
End If

On Error GoTo 0 'while developing, disable error handling
'to know if somnething is going wrong...
Set ExcelWrk = ExcelApp.Workbooks.Application.ActiveWorkbook
Set ExcelWks = ExcelApp.Worksheets.Application.ActiveSheet
' MsgBox ExcelWks.Name

ExcelWks.Columns(7).Insert
ExcelWks.Columns(7).Formula = "=IF($E1,$F1/$E1,"""")"
ExcelWks.Columns(7).NumberFormat = "0%"
ExcelWks.Range("G1") = "EGM/NBV"
End Sub
 
J

Joel

Don't use actiworksheet or workbook. Yo have to aps the workbook and or
worksheet as a parameter to the routine. If a previous macro was running you
don't have to open another excel applicxation. It s already running.

Private Sub InsertExcelCol(ExcelWrk as workbook, ExcelWks as worksheet)


Private Sub InsertExcelCol(ExcelWrk as workbook, ExcelWks as worksheet)
Dim ExcelApp As Excel.Application
Dim ExcelWrk As Excel.Workbook
Dim ExcelWks As Excel.Worksheet


ExcelWks.Columns(7).Insert
ExcelWks.Columns(7).Formula = "=IF($E1,$F1/$E1,"""")"
ExcelWks.Columns(7).NumberFormat = "0%"
ExcelWks.Range("G1") = "EGM/NBV"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top