I currently have a database that opens a spreadsheet, puts data on to a sheet than saves the sheet and closes it. It than creates 8 different spreadsheets that I have to manually add to the same spreadsheet. I would like to change this to having the different workbooks save to a different tab in the same notebook. I highlighted in Blue where the code saves the file. I know I would have to remove this code but what would I change it to so I move to a new tab? What is the code to open a new tab and start putting the new data into that new tab? Any help is appreciated.
Tom
Tom
Code:
Public goXL As Excel.Application ' The Excel Object variable
Public gbXLPresent As Boolean ' Identifies whether Excel is present upon starting
Public Const gconSaveLocation As String = "\\Amsdc\public\Client Services\Automate\HMF_Rpts\Rpts\" ' Path to save finished workbooks
Public Function XLCreate()
' ************************************************
' *** THIS SUB CREATES A NEW INSTANCE OF EXCEL ***
' ************************************************
On Error Resume Next
gbXLPresent = True
Set goXL = CreateObject("Excel.Application")
If goXL Is Nothing Then ' Check if Excel is installed
gbXLPresent = False
Else
goXL.Visible = True ' If there, make it visible
End If
End Function
' Open Excel
Call XLCreate
' Total Procedures
strSQL = "SELECT [_Periods].monord,[_Periods].yr,tbl_RptData.RefProvider,tbl_RptData.refgrp,Sum(tbl_RptData.Unit) AS u " & _
"FROM tbl_RptData INNER JOIN _Periods ON tbl_RptData.BillMonth = [_Periods].BillMonth " & _
"GROUP BY [_Periods].monord,[_Periods].yr,tbl_RptData.RefProvider,tbl_RptData.refgrp " & _
"HAVING [_Periods].monord <> 0 And [_Periods].yr > '2011' And Sum(tbl_RptData.Unit) <> 0 " & _
"ORDER BY tbl_RptData.RefProvider,[_Periods].monord;"
Set rst = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
If Not rst.EOF Then
With rst
.MoveLast
.MoveFirst
End With
strSaveName = "\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\INR\Tools\INR_RefProv_Tot_Proc.xls"
' Open Template
goXL.Workbooks.Open Filename:=strTemplate
' Initialize
iRw = 6
With goXL.Sheets("RefPhys")
.Name = "RefProv_Tot_Units"
.Cells(1, 18) = iNumMon
End With
With goXL.ActiveSheet
.Cells(1, 1).Value = "REFERRING PHYSICIAN REFERRED (Units)"
.Cells(3, 1).Value = "Fiscal " & (rst![yr])
End With
strCurRP = (rst![RefProvider])
strGrpRP = (rst![RefProvider])
' Add Data
For Z = 1 To rst.RecordCount
If ((rst![monord]) = 99) Then
iCol = 17
Else
iCol = ((rst![monord]) + 2)
End If
With goXL.ActiveSheet
.Cells(iRw, 1).Value = strCurRP
.Cells(iRw, 2).Value = (rst![refgrp])
.Cells(iRw, iCol).Value = (rst![u])
End With
rst.MoveNext
If Not rst.EOF Then
strCurRP = (rst![RefProvider])
If (strCurRP <> strGrpRP) Then
iRw = iRw + 1
strGrpRP = strCurRP
End If
End If
Next Z
' Delete Extra Rows
With goXL
.Rows("" & (iRw + 1) & ":1499").Select
Selection.Delete Shift:=xlUp
.Cells(4, 1).Select
End With
' Save Report
With goXL.ActiveWorkbook
[Blue] .SaveAs Filename:=strSaveName
.Close [/Blue]
End With
End If
rst.Close
Set rst = Nothing
' Total Charges
strSQL = "SELECT [_Periods].monord,[_Periods].yr,tbl_RptData.RefProvider,tbl_RptData.refgrp,Sum(tbl_RptData.Chgs) AS c " & _
"FROM tbl_RptData INNER JOIN _Periods ON tbl_RptData.BillMonth = [_Periods].BillMonth " & _
"GROUP BY [_Periods].monord,[_Periods].yr,tbl_RptData.RefProvider,tbl_RptData.refgrp " & _
"HAVING (([_Periods].monord <> 0) And (Sum(tbl_RptData.Chgs) <> 0)) " & _
"ORDER BY tbl_RptData.RefProvider,[_Periods].monord;"
Set rst = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
If Not rst.EOF Then
With rst
.MoveLast
.MoveFirst
End With
strSaveName = "\\salmfilesvr1\Public\Client Services\AutoRpts\_RptSets\OTHER\INR\Tools\INR_RefProv_Tot_Chgs.xls"
' Open Template
goXL.Workbooks.Open Filename:=strTemplate
' Initialize
iRw = 6
With goXL.Sheets("RefPhys")
.Name = "RefProv_Tot_Chgs"
.Cells(1, 18) = iNumMon
End With
With goXL.ActiveSheet
.Cells(1, 1).Value = "REFERRING PHYSICIAN REFERRED (Charges)"
.Cells(3, 1).Value = "Fiscal " & (rst![yr])
End With
strCurRP = (rst![RefProvider])
strGrpRP = (rst![RefProvider])
' Add Data
For Z = 1 To rst.RecordCount
If ((rst![monord]) = 99) Then
iCol = 17
Else
iCol = ((rst![monord]) + 2)
End If
With goXL.ActiveSheet
.Cells(iRw, 1).Value = strCurRP
.Cells(iRw, 2).Value = (rst![refgrp])
.Cells(iRw, iCol).Value = (rst![c])
End With
rst.MoveNext
If Not rst.EOF Then
strCurRP = (rst![RefProvider])
If (strCurRP <> strGrpRP) Then
iRw = iRw + 1
strGrpRP = strCurRP
End If
End If
Next Z
' Delete Extra Rows
With goXL
.Rows("" & (iRw + 1) & ":1499").Select
Selection.Delete Shift:=xlUp
.Cells(4, 1).Select
End With
' Save Report
With goXL.ActiveWorkbook
.SaveAs Filename:=strSaveName
.Close
End With
End If
rst.Close
Set rst = Nothing
[\code]