Solved

Export data to a specific tab within an existing excel spreadsheet

Posted on 2006-07-20
6
329 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
Independent Software Vendors: 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 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

688 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