hello. For the life of me i do not know why this simple procedure is not working. i have a folder that has 3 subfolders. i am trying to save in the last subfolder but gettign an error message" type mismatch". can someone direct me to let me know what possibly would be the issue.
the folders are created based on list name. below is the full code with the highlighted yellow section giving me an error. this is probably an easy fix.
Option Explicit
Public Const strAVPTemplate = "Clinical Capabilities Template.xls" ' template location
Public Const OutputFilePath = "S:\PSR Admit Tracking Log" ' folder path
Sub Generate_Workbooks()
Dim i As Integer, s As Integer
Dim tbl As Range
Dim wkbkGen As Workbook, wkbkTemp As Workbook
Dim strLevel As String, pathStr As String, strReportLevel As String
Dim ws As Worksheet, psrEMPLID As String
Dim livingcenterName As String, fileNamePrefix As String, fileCount As Integer, stateName As String, teamName As String
Dim rownum As Integer, reportName As String, folderPathStr As String, OuputFilePath As String
Dim prctProgress As Single
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wkbkGen = ThisWorkbook 'this is the generator workbook
pathStr = ThisWorkbook.Path 'this is the path where the generator is located
fileCount = Range("COUNT_LEVEL") 'the number of state files to generate
On Error Resume Next
Kill pathStr & "\Reports" & "\*.xls"
On Error GoTo 0
If Range("CELL_PSR_FOLDER_START") <> "" Then
Set tbl = Range("CELL_PSR_FOLDER_START").CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, tbl.Columns.Count).ClearContents
End If
'below if statement starts the process of generating the files
For i = 1 To fileCount
'sets the ranges to copy
livingcenterName = Range("starting_point").Offset(i, 0)
stateName = Range("starting_point").Offset(i, 1)
teamName = Range("starting_point").Offset(i, 2)
'this sets the prefix of the report
fileNamePrefix = Range("CELL_FILE_PREFIX")
'this is the report name being created
reportName = livingcenterName & " - " & fileNamePrefix & ".xls"
ProgressBox.Show 'displays progress bar
'this displays the status in percentage value in the progress bar
Application.StatusBar = "Generating " & livingcenterName & " Report....." & i & " of " & fileCount
prctProgress = i / fileCount * 100
ProgressBox.Increment prctProgress, "Creating report for " & livingcenterName & "- " & i & " out of " & fileCount
'this opens the template workbook by finding the path and the name of the template
Workbooks.Open ThisWorkbook.Path & "\Template\" & strAVPTemplate
Set wkbkTemp = ActiveWorkbook
'============================================================================
'Activate template
wkbkTemp.Activate
'this sets the range to copy the data unto the template
Sheets("TEMPLATE").Activate
ActiveSheet.Range("LIVINGCENTER_NAME") = livingcenterName
ActiveSheet.Range("STATE_NAME") = stateName
ActiveSheet.Range("TEAM_NAME") = teamName
ActiveSheet.Name = livingcenterName
Range("f6").Activate
ActiveWorkbook.Protect Password:="ops", Structure:=True, Windows:=False
'this saves the workbook in the report folder
ActiveWorkbook.SaveAs pathStr & "\Reports & " \ " & teamName & " \ " & stateName & " \ " & reportName"
'this designates the workbook in another path
'folderPathStr = OutputFilePath & "\" & psrNAME
'On Error Resume Next
' Kill folderPathStr & "\*.xls"
' On Error GoTo 0
'If Dir(folderPathStr, vbDirectory) = "" Then
' With wkbkGen.Sheets(1)
' ' .Range("CELL_PSR_FOLDER_START").Offset(.Range("CELL_PSR_FOLDER_COUNT"), 0) = psrNAME
' End With
'Else
' ActiveWorkbook.SaveAs folderPathStr & "\" & reportName
' End If
ActiveWorkbook.Close , False
Next i
Application.StatusBar = False
Sheets("Control").Select
ProgressBox.Hide
MsgBox "Reports have been generated succussfully!", vbInformation
End Sub
the folders are created based on list name. below is the full code with the highlighted yellow section giving me an error. this is probably an easy fix.
Option Explicit
Public Const strAVPTemplate = "Clinical Capabilities Template.xls" ' template location
Public Const OutputFilePath = "S:\PSR Admit Tracking Log" ' folder path
Sub Generate_Workbooks()
Dim i As Integer, s As Integer
Dim tbl As Range
Dim wkbkGen As Workbook, wkbkTemp As Workbook
Dim strLevel As String, pathStr As String, strReportLevel As String
Dim ws As Worksheet, psrEMPLID As String
Dim livingcenterName As String, fileNamePrefix As String, fileCount As Integer, stateName As String, teamName As String
Dim rownum As Integer, reportName As String, folderPathStr As String, OuputFilePath As String
Dim prctProgress As Single
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wkbkGen = ThisWorkbook 'this is the generator workbook
pathStr = ThisWorkbook.Path 'this is the path where the generator is located
fileCount = Range("COUNT_LEVEL") 'the number of state files to generate
On Error Resume Next
Kill pathStr & "\Reports" & "\*.xls"
On Error GoTo 0
If Range("CELL_PSR_FOLDER_START") <> "" Then
Set tbl = Range("CELL_PSR_FOLDER_START").CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, tbl.Columns.Count).ClearContents
End If
'below if statement starts the process of generating the files
For i = 1 To fileCount
'sets the ranges to copy
livingcenterName = Range("starting_point").Offset(i, 0)
stateName = Range("starting_point").Offset(i, 1)
teamName = Range("starting_point").Offset(i, 2)
'this sets the prefix of the report
fileNamePrefix = Range("CELL_FILE_PREFIX")
'this is the report name being created
reportName = livingcenterName & " - " & fileNamePrefix & ".xls"
ProgressBox.Show 'displays progress bar
'this displays the status in percentage value in the progress bar
Application.StatusBar = "Generating " & livingcenterName & " Report....." & i & " of " & fileCount
prctProgress = i / fileCount * 100
ProgressBox.Increment prctProgress, "Creating report for " & livingcenterName & "- " & i & " out of " & fileCount
'this opens the template workbook by finding the path and the name of the template
Workbooks.Open ThisWorkbook.Path & "\Template\" & strAVPTemplate
Set wkbkTemp = ActiveWorkbook
'============================================================================
'Activate template
wkbkTemp.Activate
'this sets the range to copy the data unto the template
Sheets("TEMPLATE").Activate
ActiveSheet.Range("LIVINGCENTER_NAME") = livingcenterName
ActiveSheet.Range("STATE_NAME") = stateName
ActiveSheet.Range("TEAM_NAME") = teamName
ActiveSheet.Name = livingcenterName
Range("f6").Activate
ActiveWorkbook.Protect Password:="ops", Structure:=True, Windows:=False
'this saves the workbook in the report folder
ActiveWorkbook.SaveAs pathStr & "\Reports & " \ " & teamName & " \ " & stateName & " \ " & reportName"
'this designates the workbook in another path
'folderPathStr = OutputFilePath & "\" & psrNAME
'On Error Resume Next
' Kill folderPathStr & "\*.xls"
' On Error GoTo 0
'If Dir(folderPathStr, vbDirectory) = "" Then
' With wkbkGen.Sheets(1)
' ' .Range("CELL_PSR_FOLDER_START").Offset(.Range("CELL_PSR_FOLDER_COUNT"), 0) = psrNAME
' End With
'Else
' ActiveWorkbook.SaveAs folderPathStr & "\" & reportName
' End If
ActiveWorkbook.Close , False
Next i
Application.StatusBar = False
Sheets("Control").Select
ProgressBox.Hide
MsgBox "Reports have been generated succussfully!", vbInformation
End Sub
