Insert Columns into Excel sheet from Access

I export data from an Access database into an Excel sheet. All is OK. Now I am required to insert two more columns into the Excel sheet and give them a title; all from within Access.

Here is my code after the Excel sheet was created, I open it again to do some clean-up.
'***************FORMAT EXCEL FILE**************************************
Dim appXL As Excel.Application
Dim wk As Excel.workbook
Dim ws As Excel.Worksheet
Dim NumberOfLines As Integer
Set appXL = New Excel.Application
Set wk = appXL.workbooks.Open(excelfile)
Set ws = wk.Worksheets(excelworkbooks)

'DOING MY MANIPULATIONS HERE
appXL.Visible = True

'  ---- insert two more columns named Shares and Avg.Cost Price

    With ws.Columns("E:E").Insert
    End With
Any Ideas?
liketoknowAsked:
Who is Participating?
 
TheSloathCommented:
The following would add new columns at column 2 and 5 and enter text into the first row:

With ws
    .Column(2).Insert
    .Cells(1,2) = "NewTitle1"
    .Columns(5).Insert
    .Cells(1,5) = "NewTitle2"
End With
0
 
SimonGodotCommented:
Import your data into Excel.
Go into Excel, Tools, Macro, Record a New Macro, Store it in Personal Macro Workbook.
Insert the Columns and title them
Stop Recording Macro
Save Macro as AccessColumnMacro
Run Macro From Access

Seriously, you're in Excel anyway...
0
 
liketoknowAuthor Commented:
Thanks TheSloath and SimonGodot. I was looking for a solution from within Access. Where you guys get the information from? Is it just hacking, or is there a book out there?
I personally do not have the pation to find stuff like that out.
Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.