?
Solved

Insert Columns into Excel sheet from Access

Posted on 2007-03-25
3
Medium Priority
?
337 Views
Last Modified: 2011-10-03
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?
0
Comment
Question by:liketoknow
3 Comments
 
LVL 9

Accepted Solution

by:
TheSloath earned 375 total points
ID: 18789956
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
 
LVL 2

Expert Comment

by:SimonGodot
ID: 18797934
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
 

Author Comment

by:liketoknow
ID: 18821420
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question