?
Solved

Export data to a specific tab within an existing excel spreadsheet

Posted on 2006-07-20
6
Medium Priority
?
335 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
[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
  • 2
6 Comments
 
LVL 66

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 66

Expert Comment

by:Jim Horn
ID: 17155922
Please show us the code that you have your DoCmd.TransferSpreadsheet... command in.
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 66

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month11 days, 9 hours left to enroll

752 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