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

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


"Imran J Khan" wrote:

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