Snippets for Exporting to Excel...

Most code here has been tested and, in some cases, used.  Any code untested will be marked accordingly.  We offer no support for the code or your ability to implement it.  If you require assistance please post in one of the Forums listed here.  It is strongly recommended you test the code in a copy of your database.  In other words, use at your own risk!
Add a Filter to Columns A thru Y...
xlWSh.Activate
xlWSh.Range("A7:Y7").AutoFilter
After Exporting your Data to a Template Rename to Excel Workbook...
rst.Close
Set rst = Nothing
xlWBk.SaveAs "C:/YourDirectory/NewFileName.xlsx", 51
Copy/Paste Cell Values, such as, Formulas...
With XLApp
    .Range("F2").Copy
    .Range("G3").PasteSpecial Paste:=xlPasteValues
End With
Make sure Column maintains its Date/Time Format even after Export...
xlWSh.Range("A:A").NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
Insert a Worksheet from One Workbook into Another Workbook...
Make Columns A thru Y AutoFit to the Values Inserted...
xlWSh.Activate
xlWSh.Range("A7:Y7").EntireColumn.AutoFit
The difficult I do immediately, the impossible takes a little bit longer.
Public Function InsertSP()
On Error GoTo InsertPageErr_Err
 
     Dim xlapp As Object
     Dim xlWbkNew As Object
     Dim xlWbkOld As Object
     Dim strSheetName As String
 
        Set xlapp = CreateObject("Excel.Application")
        Set xlWbkNew = xlapp.Workbooks.Open(Forms![frmExport]![txtExportPath] & "/" & Forms![frmExport]![txtNewFileName])
        Set xlWbkOld = xlapp.Workbooks.Open(Forms![frmExport]![txtExportPath] & "/" & Forms![frmExport]![txtOldFileName])
 
        strSheetName = Forms![frmExport]![txtLDSheetName]
 
     xlWbkOld.Worksheets(strSheetName).Copy After:=xlWbkNew.Worksheets(xlWbkNew.Worksheets.Count)
     xlWbkNew.Worksheets(strSheetName).Name = Forms![frmExport]![txtNewFileName] & " SP"
     xlWbkOld.Close SaveChanges:=True
     xlWbkNew.Close SaveChanges:=True
     xlapp.Quit
 
InsertPageErr_Exit:
   Set xlWbkNew = Nothing
   Set xlWbkOld = Nothing
   xlapp.Quit
   DoCmd.Hourglass False
 Exit Function
 
InsertPageErr_Err:
   MsgBox "Error # " & Err.Number & " This Worksheet already in the specified Workbook!"
 Resume InsertPageErr_Exit
 
End Function 
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.