Solved

Export data to a specific tab within an existing excel spreadsheet

Posted on 2006-07-20
6
316 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

790 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