Solved

Insert Columns into Excel sheet from Access

Posted on 2007-03-25
3
306 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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Labels prompt to print 4 32
How Do I Select a Record using a Primary Key 3 31
Run SQL Server Proc from Access 11 31
IIF help, YN field 7 22
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 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