Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert Columns into Excel sheet from Access

Posted on 2007-03-25
3
Medium Priority
?
334 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

604 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