Private Sub ExportExcel(rst As DAO.Recordset, NewFileName As String)
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim lvlColumn As Integer
Dim MasterPath As String
Dim DestinationPath As String
'Change master path to location of your master spreadsheet and
'DestinationPath to where you want the Excel files to be saved
MasterPath = "O:\Medical\Enhanced Services\ENHANCED SERVICES\Quarterly activity\Quarterly submission MASTER.xls"
DestinationPath = "O:\Medical\Enhanced Services\ENHANCED SERVICES\Quarterly activity\"
'Set up Excel document
Set xlWorkbook = xlApp.Workbooks.Open(MasterPath)
Set xlSheet = xlWorkbook.Sheets(1)
'Insert Data
With xlSheet
If Right(DestinationPath, 1) <> "\" Then
DestinationPath = DestinationPath & "\"
End If
xlWorkbook.SaveAs DestinationPath & NewFileName & ".xls"
'Insert your Code for putting things in the correct cells here.
'This is how I bring data into the sheet:
.Range("E6").CopyFromRecordset rst
.Range("C2:O31").Copy
.Range("C2:O31").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
xlWorkbook.Sheets(1).Columns(5).Delete
.Range("A1").Select
End With
'Save Excel document as [New File Name].xls
xlWorkbook.Save
xlWorkbook.Close
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlWorkbook = Nothing
End Sub
