Solved

Export data to a specific tab within an existing excel spreadsheet

Posted on 2006-07-20
6
292 Views
Last Modified: 2013-12-25
Hello,

     I am trying to export data from a table in my access db to excel.  When I use DoCmd.TransferSpreadsheet it creates a new tab in the excell workbook(which I don't want).  I need the data to be transfered to a specific tab. The table in access that I want to transfer is called "formatedoutput", the excel workbook is called "shell", and the excel worksheet in the workbook is called "database".  
     Also, I have the worksheet "database" set up in a specific format because other worksheets reference that data and use it in formulas.  Is there a way to place specific data from the access table "formatedoutput" into specified cells in "database"?

For example: "formatedoutput" is set up like this:

Bin    SLIC     Range
1      0189     MA 01000-02799, 05500-99
2      1917     RI 02800-02999
3      1754     PA 17002,04,09,14,21,29,35,44,49,51-52,54,56

"database" in excel is set up like :

Bin    SLIC           contents1                              contents2
1      0189           MA 01000-02799                    MA 05500-99
2      1917           RI 02800-02999
3      1754           PA 17002,04,09,14,21,29        PA35,44,49,51-52,54,56

I want to import the data into database and have it look like above, where the zip codes are split up if they exceed a certain length of characters.  Is there a way to do this? Thanks

-Shawn
0
Comment
Question by:shawnmoyer
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17151477
Within Access you can use DoCmd.TransferSpreadsheet's
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "formatedoutput", "c:\wherever\shell.xls", ,"database"

"Database" is where either an Excel range or a tab goes to export data.

(acSpreadsheetTypeExcel9 assumes Excel XP)

Hope this helps.
-Jim
0
 

Author Comment

by:shawnmoyer
ID: 17154558
Getting closer.  when I try to write to database, I get runtime error 3010, database already exists.  So basically I need to append to database instead of overwriting it.  
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17155922
Please show us the code that you have your DoCmd.TransferSpreadsheet... command in.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:Jim Horn
ID: 17155935
> I am trying to export data from a table in my access db to excel.
>So basically I need to append to database instead of overwriting it.  
Are you trying to move data FROM Access TO excel, or FROM Excel TO Access?
0
 
LVL 11

Accepted Solution

by:
pootle_flump earned 500 total points
ID: 17158426
Hi

Below is an example of some automation code I use for getting data from Access to Excel. If you create a local table called MyTable, a workbook called C:\Test.xls containing a worksheet called MyWorkSheet then it should work as is. If it meets your needs you can then start editing it to your specific requirements.

Option Compare Database
Option Explicit

Sub ExportData_Sheet()
On Error GoTo ExportData_Error

'DAO objects to get the data
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

'Excel objects to manipulate Excel
    Dim exApp As Excel.Application
    Dim exBook As Excel.Workbook
    Dim exSheet As Excel.Worksheet

'variables to use for formatting loops
    Dim NoOfCols As Integer
    Dim NoOfRows As Integer

'Iterant for misc loops
    Dim i As Integer
   
    Set db = Application.CurrentDb

'Get the data
    Set rs = db.OpenRecordset("SELECT * FROM MyTable")

'Instantiate the excel objects
    Set exApp = New Excel.Application
   
    Set exBook = exApp.Workbooks.Open("C:\Test.xls")
   
    exApp.Visible = True
   
    exApp.Interactive = False
   
    Set exSheet = exBook.Worksheets("MyWorkSheet")
   
    exSheet.Activate
   
    If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
   
'Populate the variables
    NoOfCols = rs.Fields.Count
    NoOfRows = rs.RecordCount

'Pop the data into Excel
    exSheet.Range("A2").CopyFromRecordset rs

'Write in the column headings
    For i = 0 To NoOfCols - 1
       
        exSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
   
    Next i

'Use our variables to format the data populated cells ONLY
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & 1).Interior.Color = vbYellow

'And again - using both this time
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & (NoOfRows + 1)).Borders.Color = RGB(0, 0, 0)

'Adjust column widths
    exSheet.Columns.EntireColumn.AutoFit

'Save it
    exBook.SaveAs "C:\Temp\Temp.xls"

ExportData_Exit:

'Very important - always account for in error trap
    exApp.Interactive = True

'Clean Up
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    Set exSheet = Nothing
    Set exBook = Nothing
    Set exApp = Nothing
   
    Exit Sub
   
ExportData_Error:
   
    MsgBox err.Description
    Resume ExportData_Exit
End Sub

Private Function ExcelCodes(ByVal intColNo As Integer) As String
 
    Dim strCol As String
 
    Do While intColNo > -1
        If intColNo > 26 Then
            strCol = Chr(64 + ((intColNo - 1) \ 26))
            intColNo = intColNo - (26 * ((intColNo - 1) \ 26))
        Else
            strCol = strCol & Chr(64 + intColNo)
            Exit Do
        End If
    Loop
 
    ExcelCodes = strCol
 
End Function

0
 

Author Comment

by:shawnmoyer
ID: 17177705
thanks for the help pootle flump.  I got it working using similar code that you suggested.  The comments were very helpfull.

-Shawn
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now