[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Transfer data to excel spreadsheet, MS Access 2003 VBA

Posted on 2011-02-18
14
Medium Priority
?
796 Views
Last Modified: 2012-05-11
I just want to know the vba code to create multiple tabs in a spreadsheet and put data in each tab

So, it would be create tab transfer data then create another tab and transfer data and so on...

I know how to open a excel spreadsheet and transfer data to one tab only using vba in ms access 2003.
0
Comment
Question by:asarda
[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
  • 4
  • 3
  • 3
  • +1
14 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34927068
<<I know how to open a excel spreadsheet and transfer data to one tab only using vba in ms access 2003.>>

How are you doing it now?  Via the TransferSpreadsheet method?
OM Gang
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1000 total points
ID: 34927136
How are you doing it now?

The following code will create a new workbook and add new worksheets.  The Sheets.Add method accepts several parameters, so you can tell it where to insert the new sheet.

You can also use the CopyFromRecordset method to create a recordset in your Access code, and paste that recordset to a cell or block of cells in your spreadsheet.  
Public Sub OutputToExcel()

    Dim xl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim rs As DAO.Recordset
    
    Set xl = CreateObject("Excel.application")
    xl.Visible = True
    
    Set wbk = xl.Workbooks.Add
        
    Set sht = wbk.Sheets.Add
    sht.Name = "New Sheet1"
    sht.Range("A1").Value = "This is a test"
    
    Set sht = wbk.Sheets.Add
    sht.Name = "New Sheet2"
    sht.Range("A2") = "This is another test"

    Set sht = wbk.Sheets.Add
    sht.Name = "From Recordset"
    Set rs = CurrentDb.OpenRecordset("qry_Numbers", , dbFailOnError)
    sht.Range("A2").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing

    Set xl = Nothing
    
End Sub

Open in new window

0
 

Author Comment

by:asarda
ID: 34927193
using
 Set objExcelWorkBk = objExcel.Workbooks.Open(....)
0
Technology Partners: 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!

 
LVL 28

Expert Comment

by:omgang
ID: 34927257
So that tells us how you are opening a workbook but not how you are transferring the data.  Look at fyed's example; that should get you started.
OM Gang
0
 

Author Comment

by:asarda
ID: 34928047
just doing something like this

objExcelWorkBk.Worksheets("Sheet1").Range("A3") = "BS"
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 34928156
asarda,

Have you tried to look at my code and understand how it works.  It really is simple to add new worksheets and fill them with data.

When you reply, it makes it easier on those of us that are trying to help if you would identify who you are replying to.
0
 

Expert Comment

by:marku24
ID: 34929820
Dim xl As Excel.Application
is coming up as invalid.  It doesn't seem to recognize Excel....... when DIMing.
0
 
LVL 28

Expert Comment

by:omgang
ID: 34929980
To early bind you'll need to set a reference to the Excel object library.
OM Gang
0
 

Expert Comment

by:marku24
ID: 34930158
I'm sorry, how do I reference the Excel object library?
0
 
LVL 28

Expert Comment

by:omgang
ID: 34930192
In the VBE (VBA editor) select the Tools menu then References.  Scroll through the list to find Microsoft Excel 12.0 Object Library and check the box next to it.
12.0 is Excel 2007.  If you have 2003, it'll be the Microsoft Excel 11.0 Oject library.  Etc.
OM Gang
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 34930587
Sorry, forgot about the reference.

I always use early binding in the development phase, then go back and change all of the declarations of those to:

    Dim xl As Object          'Excel.Application
    Dim wbk As Object      'Excel.Workbook
    Dim sht As Object        'Excel.Worksheet

When you use early binding (as mentioned by omgang) you get the benefit of intellisense, so that when you type the dot (.) after an object, you get the list of its properties and methods.  But once you are done with development, it is ideal to go back and change these declarations to Object and remove the reference to the particular object libraries (Excel, Word, PowerPoint, Outlook, ...).  This way, the code is less dependant on a particular version of Office being installed on the users machine.
0
 

Expert Comment

by:marku24
ID: 35180315
Sorry, this must have falled off my radar.  Fyed's response was helpful.  I would like to close it out and give him credit.
0
 

Author Comment

by:asarda
ID: 35180334
I am ok with giving credit to Fyed
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

649 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