T
TraciAnn via OfficeKB.com
Due to limitations in Access when exporting from Access to Excel, I need to
automate a process when the exported file is opened the first time after the
export.
Apparently Access cannot export to an Excel template file so the following is
what I currently do and what still needs to be done.
Current:
1. A named Excel workbook "CallDetail.xls"
2. A named range ($B$1:$C$2), "WeeklyCalls" the range is dynamic based on the
size of the incoming data.
3. A single worksheet "WeeklyCalls"
4. Row 1 is the header row with formatting in place
5. Access exports the data using:
stDocName = "CallDetail.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryCallDetail", stDocName, , "WeeklyCalls"
Application.FollowHyperlink stDocName, , True
6. The data is inserted into CallDetail.xls beginning in the first cell of
the named range
7. The range "WeeklyCalls" is adjusted to accomodate all imported data.
Needed:
1. Since Access cannot export to a template, I need the initial file
(CallDetails.xls) to remain "untouched", saving the exported data to a file
name of the format "CallDetail mmddyy.xls"
2. Access exports column headers as the first row. Since these headers are
not user friendly names and do not contain formatting, Row 2 should be
deleted.
3. Subtotal data for each break in Column A (Technician), Sum Column D
(Duration)
End Result:
After executing the command in Access which exports the data to an existing
workbook "CallDetail.xls" and then opens the workbook, the Excel procedures
needs to be seamless to the user.
What is the best way to execute the steps under "Needed"?
Thanks everyone for your help!
--
automate a process when the exported file is opened the first time after the
export.
Apparently Access cannot export to an Excel template file so the following is
what I currently do and what still needs to be done.
Current:
1. A named Excel workbook "CallDetail.xls"
2. A named range ($B$1:$C$2), "WeeklyCalls" the range is dynamic based on the
size of the incoming data.
3. A single worksheet "WeeklyCalls"
4. Row 1 is the header row with formatting in place
5. Access exports the data using:
stDocName = "CallDetail.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryCallDetail", stDocName, , "WeeklyCalls"
Application.FollowHyperlink stDocName, , True
6. The data is inserted into CallDetail.xls beginning in the first cell of
the named range
7. The range "WeeklyCalls" is adjusted to accomodate all imported data.
Needed:
1. Since Access cannot export to a template, I need the initial file
(CallDetails.xls) to remain "untouched", saving the exported data to a file
name of the format "CallDetail mmddyy.xls"
2. Access exports column headers as the first row. Since these headers are
not user friendly names and do not contain formatting, Row 2 should be
deleted.
3. Subtotal data for each break in Column A (Technician), Sum Column D
(Duration)
End Result:
After executing the command in Access which exports the data to an existing
workbook "CallDetail.xls" and then opens the workbook, the Excel procedures
needs to be seamless to the user.
What is the best way to execute the steps under "Needed"?
Thanks everyone for your help!
--