I used transferspreadsheet to transfer a query to an Excel Spreadsheet...i wrote the following code to format the spreadsheet...the code seems to work and format everything as expected...BUT...i was expecting the Excel spreadsheet to be visible at the end of the code...but it isn't...also if i try to manually open the newly formatted spreadsheet (via Windows Explorer) it won't open either...but if i open any other spreadsheet then the formatted spreadsheet is in the other window...here's my code...any thoughts on what i am doing wrong?
Thanks
Thanks
Code:
Sub OpenAndFormatExcel()
On Error Resume Next
Dim filePath As String
filePath = CurrentProject.Path & "\UnitSalesByCust" & Forms!frmMain.cboCust & ".xls"
Dim xl As Excel.Application
Dim xlBook As Excel.workbook
Dim xlSheet As Excel.worksheet
Set xl = CreateObject("Excel.Application")
Set xlBook = GetObject(filePath)
xl.Visible = True
xlBook.Windows(1).Visible = True
Set xlSheet = xlBook.Worksheets(1)
Range("A1:P1").Font.Bold = True
For Each cell In Range(Cells(1, "F"), Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F"))
If Right(cell.Value, 5) = "TOTAL" Then Range(Cells(cell.Row, "A"), Cells(cell.Row, "P")).Font.Bold = True
Next cell
Rows("1:3").Insert
Range("C1") = Range("B5") & " (" & Range("A5") & ")"
Range("C2") = Format(Range("O5"), "mm/dd/yy") & " - " & Format(Range("P5"), "mm/dd/yy")
With Range("C1:N1")
.HorizontalAlignment = xlCenter
.Merge
.Font.Size = 24
.Font.Bold = True
End With
With Range("C2:N2")
.HorizontalAlignment = xlCenter
.Merge
.Font.Size = 12
.Font.Bold = True
End With
If Forms!frmMain.chkIncludeGM = -1 Then
Range("J:J,N:N").NumberFormat = "0.00%"
Range("A:B,O:P").Delete Shift:=xlToLeft
Else
Range("A:B,O:P,J:J,N:N").Delete Shift:=xlToLeft
End If
Cells.Columns.AutoFit
xl.Application.Goto Range("A3")
xlBook.Save
Set xl = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End Sub