Solved

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

Posted on 2009-05-08
6
295 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
ID: 24338143
I recommend using the DoCmd.TransferSpreadsheet function.
DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "TableName", "FilePathAndName", True ' HasFieldNames(Boolean)

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24338177
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
ID: 24338218
Sorry, I left out the acExport.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableName", "FilePathAndName", True

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Closing Comment

by:jmcclosk
ID: 31579557
It works great!  Thanks!
0
 

Author Comment

by:jmcclosk
ID: 24338405
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
ID: 24338415
Absolutely right. You are welcome.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

828 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