| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Column into active Excel Sheet from Access
Hello,
I have an Access application that exports several different data sets (reports) to Excel. However, I need to insert in one worksheet a calculated column G (Column G = ColumnE/ColumnF). The worksheet goes upto M, so column G already has data that needs to be shifted one column to the right. I also need have a need title for Column G. The other reports do not require this, so I do not want to change our standard Access function that creates worksheets. I need to insert column G after the Access function has finished creating the worksheet. The Workbook and Worksheet should be the active ones. The name of the worksheet will be always be the same for this report, but the workbook name will vary ("Book1","Book2",etc). I would appreciate any help on how to do this. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert Column into active Excel Sheet from Access
Do you mean
Sub Macro1() ActiveSheet.Columns(7).Insert ActiveSheet.Range("G1") = "Title" End Sub -- If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Hello, I have an Access application that exports several different data sets (reports) to Excel. However, I need to insert in one worksheet a calculated column G (Column G = ColumnE/ColumnF). The worksheet goes upto M, so column G already has data that needs to be shifted one column to the right. I also need have a need title for Column G. The other reports do not require this, so I do not want to change our standard Access function that creates worksheets. I need to insert column G after the Access function has finished creating the worksheet. The Workbook and Worksheet should be the active ones. The name of the worksheet will be always be the same for this report, but the workbook name will vary ("Book1","Book2",etc). I would appreciate any help on how to do this. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert Column into active Excel Sheet from Access
I assume you are using a macro to export; Ofcourse you need to refer to the
Excel application object...to access activesheet. "Jacob Skaria" wrote: Do you mean Sub Macro1() ActiveSheet.Columns(7).Insert ActiveSheet.Range("G1") = "Title" End Sub -- If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Hello, I have an Access application that exports several different data sets (reports) to Excel. However, I need to insert in one worksheet a calculated column G (Column G = ColumnE/ColumnF). The worksheet goes upto M, so column G already has data that needs to be shifted one column to the right. I also need have a need title for Column G. The other reports do not require this, so I do not want to change our standard Access function that creates worksheets. I need to insert column G after the Access function has finished creating the worksheet. The Workbook and Worksheet should be the active ones. The name of the worksheet will be always be the same for this report, but the workbook name will vary ("Book1","Book2",etc). I would appreciate any help on how to do this. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert Column into active Excel Sheet from Access
Thank you Jacob, That did help, but I need a little more.
Btw, I am not using a Macro. I am using the following after modifing code I found on the web. (Sorry, my system crashed before I could get details on who posted it) Private Sub testExcelInsert() 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).Value = "=$e1/$f1" ExcelWks.Range("G1") = "EGM/NBV" End Sub Thanks to you I have been able to insert a column and title it, but I also need to format it to percentage and also stop the column G values being changed to =$e1/$f1 when it comes to end of data. Currently I have a whole lot of #DIV/0!'s. Imran "Jacob Skaria" wrote: I assume you are using a macro to export; Ofcourse you need to refer to the Excel application object...to access activesheet. "Jacob Skaria" wrote: Do you mean Sub Macro1() ActiveSheet.Columns(7).Insert ActiveSheet.Range("G1") = "Title" End Sub -- If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Hello, I have an Access application that exports several different data sets (reports) to Excel. However, I need to insert in one worksheet a calculated column G (Column G = ColumnE/ColumnF). The worksheet goes upto M, so column G already has data that needs to be shifted one column to the right. I also need have a need title for Column G. The other reports do not require this, so I do not want to change our standard Access function that creates worksheets. I need to insert column G after the Access function has finished creating the worksheet. The Workbook and Worksheet should be the active ones. The name of the worksheet will be always be the same for this report, but the workbook name will vary ("Book1","Book2",etc). I would appreciate any help on how to do this. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert Column into active Excel Sheet from Access
ExcelWks.Columns(7).Insert
ExcelWks.Columns(7).Formula = "=IF($F1,$E1/$F1,"""")" ExcelWks.Columns(7).NumberFormat = "0.00%" ExcelWks.Range("G1") = "EGM/NBV" If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Thank you Jacob, That did help, but I need a little more. Btw, I am not using a Macro. I am using the following after modifing code I found on the web. (Sorry, my system crashed before I could get details on who posted it) Private Sub testExcelInsert() 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).Value = "=$e1/$f1" ExcelWks.Range("G1") = "EGM/NBV" End Sub Thanks to you I have been able to insert a column and title it, but I also need to format it to percentage and also stop the column G values being changed to =$e1/$f1 when it comes to end of data. Currently I have a whole lot of #DIV/0!'s. Imran "Jacob Skaria" wrote: I assume you are using a macro to export; Ofcourse you need to refer to the Excel application object...to access activesheet. "Jacob Skaria" wrote: Do you mean Sub Macro1() ActiveSheet.Columns(7).Insert ActiveSheet.Range("G1") = "Title" End Sub -- If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Hello, I have an Access application that exports several different data sets (reports) to Excel. However, I need to insert in one worksheet a calculated column G (Column G = ColumnE/ColumnF). The worksheet goes upto M, so column G already has data that needs to be shifted one column to the right. I also need have a need title for Column G. The other reports do not require this, so I do not want to change our standard Access function that creates worksheets. I need to insert column G after the Access function has finished creating the worksheet. The Workbook and Worksheet should be the active ones. The name of the worksheet will be always be the same for this report, but the workbook name will vary ("Book1","Book2",etc). I would appreciate any help on how to do this. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert Column into active Excel Sheet from Access
Thank you again Jacob. Your code completed the function.
Imran "Jacob Skaria" wrote: ExcelWks.Columns(7).Insert ExcelWks.Columns(7).Formula = "=IF($F1,$E1/$F1,"""")" ExcelWks.Columns(7).NumberFormat = "0.00%" ExcelWks.Range("G1") = "EGM/NBV" If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Thank you Jacob, That did help, but I need a little more. Btw, I am not using a Macro. I am using the following after modifing code I found on the web. (Sorry, my system crashed before I could get details on who posted it) Private Sub testExcelInsert() 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).Value = "=$e1/$f1" ExcelWks.Range("G1") = "EGM/NBV" End Sub Thanks to you I have been able to insert a column and title it, but I also need to format it to percentage and also stop the column G values being changed to =$e1/$f1 when it comes to end of data. Currently I have a whole lot of #DIV/0!'s. Imran "Jacob Skaria" wrote: I assume you are using a macro to export; Ofcourse you need to refer to the Excel application object...to access activesheet. "Jacob Skaria" wrote: Do you mean Sub Macro1() ActiveSheet.Columns(7).Insert ActiveSheet.Range("G1") = "Title" End Sub -- If this post helps click Yes --------------- Jacob Skaria "Imran J Khan" wrote: Hello, I have an Access application that exports several different data sets (reports) to Excel. However, I need to insert in one worksheet a calculated column G (Column G = ColumnE/ColumnF). The worksheet goes upto M, so column G already has data that needs to be shifted one column to the right. I also need have a need title for Column G. The other reports do not require this, so I do not want to change our standard Access function that creates worksheets. I need to insert column G after the Access function has finished creating the worksheet. The Workbook and Worksheet should be the active ones. The name of the worksheet will be always be the same for this report, but the workbook name will vary ("Book1","Book2",etc). I would appreciate any help on how to do this. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert a new Worksheet AFTER the existing (Active) sheet | Excel Worksheet Functions | |||
| Macro with input box to Select a column on active sheet | Excel Programming | |||
| Look up date on another sheet and do count of active cells (column | Excel Worksheet Functions | |||
| Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
| Insert Whole Row in Excel when one column is updated from Access. | Excel Programming | |||
Linear Mode
