Link to home
Start Free TrialLog in
Avatar of LeLeBrown
LeLeBrown

asked on

Can anyone tell me why I am getting this error when I save excel sheet in access vba. This all of a sudden stopeed working.

 I am getting a runtime error on this statement.  I have attached  my code.  Please help, on deadline.  This was working fine all day and then it just stopped working.

1004   - Saveas Method of Workbook Class Failed


   objXLBook.SaveAs (path & haCode & FileName), 56
Private Sub CmdCreateApps_Click()

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim rsSec As Recordset
Dim rsExtDate As Recordset
Dim rsPHA As Recordset
Dim QDefExtDate As DAO.QueryDef
Dim QDefPHACode As DAO.QueryDef
Dim counter As String
Dim Counter1 As String
Dim MoreYearsCntr As Integer
Dim MoreYrsCntr As String
Dim MoreYearsFirst As Integer
Dim MoreYrsFirst As String
Dim HeaderPlace As Integer
Dim Headerpl As String
Dim SubTotalCntr As Integer
Dim SaveFiscalYear As Integer
Dim FiscalYearVar As Integer
Dim SubTotCntr As String
Dim strSQL As String
Dim strSQL2 As String
Dim objXLApp As Object
Dim objXLBook As Object
Dim HAName As String
Dim FileName As String
Dim path As String
Dim haCode As String
Dim HUDCaps As String
Dim PHACodeBeg As String
Dim PHACodeEnd As String
Dim PHAFound As Boolean
Dim ExtDateWords As String
Dim ExtDateWords1 As String
Dim CurrentDate As Date
Dim CurrentMonth As String
Dim CurrentDay As String
Dim CurrentYear As String
Dim ExtDateMonth As String
Dim ExtDateDay As String
Dim ExtDateYear As String


'Verbage for first line of  Sheet to indicate LOCCS extract date
ExtDateWords = "The information provided in this Report is as of"
ExtDateWords1 = "from 2011 Formula Tables and the PIC Certification Table"

Set db = CurrentDb

ExtDateMonth = Month(Date)
ExtDateDay = Day(Date)
ExtDateYear = Year(Date)

FileName = "CertificationCompare.xlsx"
path = "J:PCR\2012 Files\Certification Compare Reports\"

Set db = CurrentDb

PHACodeBeg = "ak001"
PHACodeEnd = "ak001"

Set QDefPHACode = db.QueryDefs("FindHACode")
QDefPHACode.Parameters("PHACode1") = PHACodeBeg
QDefPHACode.Parameters("PHACode2") = PHACodeEnd
Set rsPHA = QDefPHACode.OpenRecordset
Do Until rsPHA.EOF
 
 'Make sure no other instances of Excel are open
 ExcelRunning = IsExcelRunning()
 If ExcelRunning Then
 'attach to an existing instance of Excel
   Set objXLApp = GetObject(, "Excel.Application")
 Else
 'create a new instance of Excel
   Set objXLApp = CreateObject("Excel.Application")
 End If

 'Add workbook
 Set objXLBook = objXLApp.Workbooks.Add
 'open new worksheet
 Set objSheet = objXLBook.ActiveSheet
   haCode = rsPHA!haCode
   
 ' FileName = haCode & "CertificationCompare.xls"
  
 'Developments Certified in 2011 but not on Current PIC Cert Table
 'strSQL2 = "Select * " _
 '       & "from  DevelopmentsIn2011NotInCurrent " _
 '       & "where HACODE = '" & haCode & "';"
 ' Set rs2 = db.OpenRecordset(strSQL2)


  strSQL = "Select * " _
        & "from tblFinalTable " _
        & "where [2011_participant_code] = '" & haCode & "';"
  Set rs = db.OpenRecordset(strSQL)
  If rs.BOF Then
    PHAFound = False
  Else
    PHAFound = True
  End If
  
   objXLBook.ActiveSheet.Range("a1").Value = "Year Over Year Capital Fund Formula Characteristics Comparison Tool "
   objXLBook.ActiveSheet.Range("a1").Font.Size = 12
   objXLBook.ActiveSheet.Range("a1").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("a1").ColumnWidth = 12
   objXLBook.ActiveSheet.Range("a1").Font.Bold = True
   objXLBook.ActiveSheet.Range("a1", "e1").Merge
   objXLBook.ActiveSheet.Range("a1").RowHeight = 20
   
   objXLBook.ActiveSheet.Range("a2").Value = rsPHA!haCode
   objXLBook.ActiveSheet.Range("a2").Font.Size = 12
   objXLBook.ActiveSheet.Range("a2").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("a2").ColumnWidth = 12
   objXLBook.ActiveSheet.Range("a2").Font.Bold = True
   'objXLBook.ActiveSheet.Range("a2", "c2").Merge
   objXLBook.ActiveSheet.Range("a2").RowHeight = 20
   
   objXLBook.ActiveSheet.Range("a3").Value = "Data as of " & ExtDateMonth & "/" & ExtDateDay & "/" & ExtDateYear & ""
   objXLBook.ActiveSheet.Range("a3").Font.Size = 12
   objXLBook.ActiveSheet.Range("a3").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("a3").ColumnWidth = 12
   objXLBook.ActiveSheet.Range("a3").Font.Bold = True
   objXLBook.ActiveSheet.Range("a3", "c3").Merge
   objXLBook.ActiveSheet.Range("a3").RowHeight = 20
   
   objXLBook.ActiveSheet.Range("a5").Value = "2011 "
   objXLBook.ActiveSheet.Range("a5").Font.Size = 14
   objXLBook.ActiveSheet.Range("a5").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("a5").ColumnWidth = 6
   objXLBook.ActiveSheet.Range("a5").Font.Bold = True
   'objXLBook.ActiveSheet.Range("a5", "c1").Merge
   objXLBook.ActiveSheet.Range("a5").RowHeight = 32
   
         
   objXLBook.ActiveSheet.Range("b7").Value = "Development Number"
   objXLBook.ActiveSheet.Range("b7").Font.Size = 11
   objXLBook.ActiveSheet.Range("b7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("b7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("b7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("b7").ColumnWidth = 13
   objXLBook.ActiveSheet.Range("b7").Font.Bold = True
   With objSheet.Range("B:B")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("c7").Value = "Standing Units"
   objXLBook.ActiveSheet.Range("c7").Font.Size = 11
   objXLBook.ActiveSheet.Range("c7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("c7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("c7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("c7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("c7").Font.Bold = True
   With objSheet.Range("C:C")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("d7").Value = "Removed Units"
   objXLBook.ActiveSheet.Range("d7").Font.Size = 11
   objXLBook.ActiveSheet.Range("d7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("d7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("d7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("d7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("d7").Font.Bold = True
   With objSheet.Range("D:D")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("e7").Value = "Non ACC Units"
   objXLBook.ActiveSheet.Range("e7").Font.Size = 11
   objXLBook.ActiveSheet.Range("e7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("e7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("e7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("e7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("e7").Font.Bold = True
   With objSheet.Range("e:e")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("f7").Value = "Non Dwellinig Units"
   objXLBook.ActiveSheet.Range("f7").Font.Size = 11
   objXLBook.ActiveSheet.Range("f7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("f7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("f7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("f7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("f7").Font.Bold = True
   With objSheet.Range("f:f")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
   objXLBook.ActiveSheet.Range("g7").Value = "Standing Bdr Cnt"
   objXLBook.ActiveSheet.Range("g7").Font.Size = 11
   objXLBook.ActiveSheet.Range("g7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("g7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("g7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("g7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("g7").Font.Bold = True
   With objSheet.Range("g:g")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("h7").Value = "Removed Bdr Cnt"
   objXLBook.ActiveSheet.Range("h7").Font.Size = 11
   objXLBook.ActiveSheet.Range("h7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("h7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("h7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("h7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("h7").Font.Bold = True
   With objSheet.Range("h:h")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("i7").Value = "DOFA Date"
   objXLBook.ActiveSheet.Range("i7").Font.Size = 11
   objXLBook.ActiveSheet.Range("i7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("i7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("i7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("i7").ColumnWidth = 10
   objXLBook.ActiveSheet.Range("i7").Font.Bold = True
   With objSheet.Range("i:i")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
  'objXLBook.ActiveSheet.Range("j7").Value = "H6 Units"
  ' objXLBook.ActiveSheet.Range("j7").Font.Size = 11
  ' objXLBook.ActiveSheet.Range("j7").Font.ColorIndex = 1
  ' objXLBook.ActiveSheet.Range("j7").Interior.ColorIndex = 15
  ' objXLBook.ActiveSheet.Range("j7").Borders.ColorIndex = 1
  ' objXLBook.ActiveSheet.Range("j7").ColumnWidth = 6
  ' objXLBook.ActiveSheet.Range("j7").Font.Bold = True
  ' With objSheet.Range("j:j")
  '      .HorizontalAlignment = xlGeneral
  '      .VerticalAlignment = xlBottom
  '      .WrapText = True
  '      .Orientation = 0
  '      .AddIndent = False
  '      .IndentLevel = 0
  '      .ShrinkToFit = False
  '      .ReadingOrder = xlContext
  '      .MergeCells = False
  '  End With
   
   objXLBook.ActiveSheet.Range("j5").Value = "2012 "
   objXLBook.ActiveSheet.Range("j5").Font.Size = 14
   objXLBook.ActiveSheet.Range("j5").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("j5").ColumnWidth = 6
   objXLBook.ActiveSheet.Range("j5").Font.Bold = True
   'objXLBook.ActiveSheet.Range("k7", "m7").Merge
   objXLBook.ActiveSheet.Range("j5").RowHeight = 32
   
         
   objXLBook.ActiveSheet.Range("k7").Value = "Development Number"
   objXLBook.ActiveSheet.Range("k7").Font.Size = 11
   objXLBook.ActiveSheet.Range("k7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("k7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("k7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("k7").ColumnWidth = 13
   objXLBook.ActiveSheet.Range("k7").Font.Bold = True
   With objSheet.Range("k:k")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("l7").Value = "Standing Units"
   objXLBook.ActiveSheet.Range("l7").Font.Size = 11
   objXLBook.ActiveSheet.Range("l7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("l7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("l7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("l7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("l7").Font.Bold = True
   With objSheet.Range("l:l")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("m7").Value = "Removed Units"
   objXLBook.ActiveSheet.Range("m7").Font.Size = 11
   objXLBook.ActiveSheet.Range("m7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("m7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("m7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("m7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("m7").Font.Bold = True
   With objSheet.Range("m:m")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("n7").Value = "Non ACC Units"
   objXLBook.ActiveSheet.Range("n7").Font.Size = 11
   objXLBook.ActiveSheet.Range("n7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("n7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("n7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("n7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("n7").Font.Bold = True
   With objSheet.Range("n:n")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("o7").Value = "Non Dwellinig Units"
   objXLBook.ActiveSheet.Range("o7").Font.Size = 11
   objXLBook.ActiveSheet.Range("o7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("o7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("o7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("o7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("o7").Font.Bold = True
   With objSheet.Range("o:o")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
   objXLBook.ActiveSheet.Range("p7").Value = "Standing Bdr Cnt"
   objXLBook.ActiveSheet.Range("p7").Font.Size = 11
   objXLBook.ActiveSheet.Range("p7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("p7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("p7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("p7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("p7").Font.Bold = True
   With objSheet.Range("p:p")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("q7").Value = "Removed Bdr Cnt"
   objXLBook.ActiveSheet.Range("q7").Font.Size = 11
   objXLBook.ActiveSheet.Range("q7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("q7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("q7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("q7").ColumnWidth = 9
   objXLBook.ActiveSheet.Range("q7").Font.Bold = True
   With objSheet.Range("q:q")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   objXLBook.ActiveSheet.Range("r7").Value = "DOFA Date"
   objXLBook.ActiveSheet.Range("r7").Font.Size = 11
   objXLBook.ActiveSheet.Range("r7").Font.ColorIndex = 1
   objXLBook.ActiveSheet.Range("r7").Interior.ColorIndex = 15
   objXLBook.ActiveSheet.Range("r7").Borders.ColorIndex = 1
   objXLBook.ActiveSheet.Range("r7").ColumnWidth = 10
   objXLBook.ActiveSheet.Range("r7").Font.Bold = True
   With objSheet.Range("r:r")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
   'objXLBook.ActiveSheet.Range("t7").Value = "H6 Units"
   'objXLBook.ActiveSheet.Range("t7").Font.Size = 11
   'objXLBook.ActiveSheet.Range("t7").Font.ColorIndex = 1
   'objXLBook.ActiveSheet.Range("t7").Interior.ColorIndex = 15
   'objXLBook.ActiveSheet.Range("t7").Borders.ColorIndex = 1
   'objXLBook.ActiveSheet.Range("t7").ColumnWidth = 6
   'objXLBook.ActiveSheet.Range("t7").Font.Bold = True
   'With objSheet.Range("t:t")
   '     .HorizontalAlignment = xlGeneral
   '     .VerticalAlignment = xlBottom
   '     .WrapText = True
   '     .Orientation = 0
   '     .AddIndent = False
   '     .IndentLevel = 0
   '     .ShrinkToFit = False
   '     .ReadingOrder = xlContext
   '     .MergeCells = False
   ' End With
  
  counter = 7

  Do Until rs.EOF
   counter = counter + 1

   objSheet.Activate
   
   'objXLBook.Sheets("PHAProjectData").Activate
   objXLBook.ActiveSheet.Range("b" + counter).Value = rs![2011_development_number]
   objXLBook.ActiveSheet.Range("c" + counter).Value = rs![2011_standing_unit_count]
   objXLBook.ActiveSheet.Range("d" + counter).Value = rs![2011_removed_unit_count]
   objXLBook.ActiveSheet.Range("e" + counter).Value = rs![2011_acc_no_unit_count]
   objXLBook.ActiveSheet.Range("f" + counter).Value = rs![2011_non_dwelling_unit_count]
   objXLBook.ActiveSheet.Range("g" + counter).Value = rs![2011_tot_bedroom_count_standing]
   objXLBook.ActiveSheet.Range("h" + counter).Value = rs![2011_tot_bedroom_count_rmi]
   objXLBook.ActiveSheet.Range("i" + counter).Value = rs![2011_dofa_actual_date]
   objXLBook.ActiveSheet.Range("i" + counter).NumberFormat = "mm/dd/yyyy"
   'objXLBook.ActiveSheet.Range("j" + counter).Value = rs![2011_hope6_replacement_units_planned]
   objXLBook.ActiveSheet.Range("k" + counter).Value = rs!development_number
   objXLBook.ActiveSheet.Range("l" + counter).Value = rs!standing_unit_count
   objXLBook.ActiveSheet.Range("m" + counter).Value = rs!removed_unit_count
   objXLBook.ActiveSheet.Range("n" + counter).Value = rs!acc_no_unit_count
   objXLBook.ActiveSheet.Range("o" + counter).Value = rs!non_dwelling_unit_count
   objXLBook.ActiveSheet.Range("p" + counter).Value = rs!tot_bedroom_count_standing
   objXLBook.ActiveSheet.Range("q" + counter).Value = rs!tot_bedroom_count_rmi
   objXLBook.ActiveSheet.Range("r" + counter).Value = rs!dofa_actual_date
   objXLBook.ActiveSheet.Range("r" + counter).NumberFormat = "mm/dd/yyyy"
   'objXLBook.ActiveSheet.Range("t" + counter).Value = rs!hope6_replacement_units_planned
   
      
  rs.MoveNext
  Loop

   
If PHAFound = True Then

'Paper Print Size and Orientation
 ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaperLegal
        .Zoom = 80
     End With
     

 objXLApp.Application.Visible = False


 
   objXLBook.SaveAs (path & haCode & FileName), 56
   'objXLBook.SaveAs "J:PCR\2012 Files\Certification Compare Reports\filename", 56
End If

 objXLApp.ActiveWorkbook.Close SaveChanges:=False

 rsPHA.MoveNext
 Loop
 'Quit Application
  objExcel.Quit
 
'***************************
 MsgBox ("Application Created Successfully")

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of X Layer
X Layer
Flag of Slovenia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of geoffkk
geoffkk
Flag of Australia image
Not sure what haCode is.
Does it have any characters that could violate filename conventions?

Agree also re J:\PCR...