Shouldn't the last workbook/worksheet created be the Active one?
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 |
Shouldn't the last workbook/worksheet created be the Active one?
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 |
All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com