Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export data to a specific tab within an existing excel spreadsheet

Posted on 2006-07-20
6
Medium Priority
?
339 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…
Suggested Courses

636 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