Exporting data into pre-formatted Excel spreadsheet

[Guest]

I have an access database set up to where users enter information into a
form. From the main switchboard, there is an option to run a report, and
when clicked, it will take you to a reports menu, and you can select the type
of report you wish to run, then it will create the report in Excel. For
example, if you wish to run a report on a specific date range, you select "By
Date", then you will be prompted for the beginning and ending dates, then a
pop up window will tell you that the report has been created and is currently
open in Excel. Basically, it just creates a new spreadsheet. The sheet is
then ready for you to begin formatting it, etc. What I need to do though, is
have this data dumped into a preformatted Excel spreadsheet and saved
elsewhere so that when they run the report, it is already formatted, and can
then just be printed without the need to format the whole spreadsheet
(because it is a lot of formatting for margins, font type, size, header
names, number format, column width, etc...), but at the same time not saving
over my preformatted template. I've already got the spreadsheet formatted...
I just dont know how to get it to tie together. Below is the code I have
behind the report currently. I'm not that great with vba... can anyone help
me please?

Option Compare Database

Private Sub Report_Activate()

DoCmd.Close acReport, "rptAccountingReport"

End Sub

Private Sub Report_Open(Cancel As Integer)

Call procDownloadDataToExcel("Report By Date", "qryDate")

End Sub
Private Sub procDownloadDataToExcel(FileNameToUse As String, QueryToRun As
String)

'Create an Excel spreadsheet download.

On Error GoTo Err_procDownloadDataToExcel

Dim x, XcelFileName As String, UseDir As String
XcelFileName = FileNameToUse & " " & Format(Date, "mm-dd-yyyy") & ".xls"
UseDir = "L:\Gascon\Common\Contract Transmittal Database\Contract
Transmittal Reports"

DoCmd.SetWarnings False

Call procVerifyDirectory(UseDir)

x = UseDir & XcelFileName 'Use the variable x temporarily
to store the drive\path\filename.
DoCmd.OutputTo acOutputQuery, QueryToRun, acFormatXLS, x, True

AppActivate "Microsoft Access"
MsgBox "The file " & x & " has been created and is opened in Excel.", ,
"Spreadsheet Created."

DoCmd.SetWarnings True

Exit_procDownloadDataToExcel:
Exit Sub

Err_procDownloadDataToExcel:
If Err.Number = 2302 Then 'This specific
file already in use by Excel
MsgBox "The file '" & XcelFileName & "' could not be created because
it is currently opened in Excel.", vbExclamation, "Sorry!"
Else
MsgBox "An error has occurred on this form. Error Number " &
Err.Number & " - " & Err.Description, vbCritical, "Error in
procDownloadDataToExcel on form 'frmMainMenu'."
Resume Exit_procDownloadDataToExcel
End If

DoCmd.SetWarnings True

End Sub

Public Sub procVerifyDirectory(NameOfDir As String)
'Check for folder or create it if it does not exist.

Dim x
x = Dir(NameOfDir, vbDirectory)
If x = "" Then MkDir NameOfDir

End Sub

 

[PC Datasheet]

Hi Stacie,

The data in Access is in table format whether you export from a table or
query. You need to export the Access data to a worksheet where it will be in
table format just like it is in Access. Use the Transferspreadsheet function
to export. Your pre-formatted Excel worksheet then needs to have formulas
that bring in the data from the other worksheet.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com