Solved

Export Access Table to a specific sheet in an existing Excel file

Posted on 2009-05-08
6
292 Views
Last Modified: 2013-11-27
I currently have code listed below in the form of a Microsoft Access application that copies an Excel Spreadsheet from a centralized template and pastes a new copy of it to a local location for the user to enter data on for a current incident, then save it or e-mail it without corrupting the original template.  A lot of the data requested in the Excel template is already in my Access DB and I have written a query (qry_Investigate) that asks the user what incident# they are working with and creates a local table (local_tbl_Investigate_Temp) isolating this information for them.  I would like to incorporate into my code the ability to export the information from this Access table into the second worksheet of the opened local Excel workbook (F:\FM Accident Invest Write-in.xls) from Sheet2!A1:Sheet2!P1 (16 data items), then have the template on Sheet1 of the workbook populate the associated fields so the end user does not have to re-type data already stored in the DB.

Please note: I use this code for a lot of forms that I pull from a central location.  So, I have a table (tbl_Path) that stores the locations of each template.
Private Sub Run_Report_Click()
 

Dim db As Database

Dim path_RST As Recordset
 

Dim path_STR As String

Dim image1_Src As String

Dim image1_Dst As String

Dim fname1 As String

Dim pfname1 As String

Dim msg As Integer

Dim msg2 As Integer

Dim fs
 

Set db = CurrentDb

Set path_RST = db.OpenRecordset("tbl_Path", dbOpenDynaset)

Set fs = CreateObject("Scripting.FileSystemObject")
 

image1_Src = path_RST![path]

image1_Dst = "F:\"

fs.CopyFile image1_Src, image1_Dst

fname1 = Right(path_RST![path], (Len(path_RST![path]) - 30))

pfname1 = image1_Dst & fname1

msg = MsgBox("The copy of this spreadsheet you are viewing has been saved to your (F:) drive and named: " & fname1, vbOKOnly, "Working Copy")

    

FollowHyperlink pfname1

End Sub

Open in new window

0
Comment
Question by:jmcclosk
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
I recommend using the DoCmd.TransferSpreadsheet function.
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "TableName", "FilePathAndName", True ' HasFieldNames(Boolean)

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
this code will export tableX to specified name of sheet of  the excel file

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      "tableX", "C:\myExcel.xls, True, "NameOfsheet"
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Sorry, I left out the acExport.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableName", "FilePathAndName", True

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Closing Comment

by:jmcclosk
Comment Utility
It works great!  Thanks!
0
 

Author Comment

by:jmcclosk
Comment Utility
Both of you had the same solution. cwood-wm-com posted it four minutes faster, but left out a needed part of the command (which was corrected).  Since it was so close time-wise, and you both had the same answer, I felt it was only right to give the points to capricorn1 because he got all of it on the first try. Sorry...
Thanks to both of you!
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
Absolutely right. You are welcome.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

16 Experts available now in Live!

Get 1:1 Help Now