Solved

Insert Columns into Excel sheet from Access

Posted on 2007-03-25
3
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
TheSloath earned 125 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

688 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