I have a system that opens each morning, runs some checks and exports 3 queries to excel files. This all works nicely.
I have tried to add some code to open each of these files, immediately after they are exported to apply some formatting to them, save, close then move onto the next export.
But I am having some problems, it works absolutely fine for the first export, opens, formats, saves closes no problem at all. But it doesn't work for the rest of the files, it opens the file, but then as soon as it comes to do any formatting or anything I get the following error message appear:
"Method 'Cells' of object '_Global' failed"
The message appears for whatever is the first part of the formatting code that is trying to run, I just happened to make note of the above after I had been trying a couple of things, but once it's failed it then fails on all sections you try and run.
Any idea's on where I've gone wrong?
Thanks
I have tried to add some code to open each of these files, immediately after they are exported to apply some formatting to them, save, close then move onto the next export.
But I am having some problems, it works absolutely fine for the first export, opens, formats, saves closes no problem at all. But it doesn't work for the rest of the files, it opens the file, but then as soon as it comes to do any formatting or anything I get the following error message appear:
"Method 'Cells' of object '_Global' failed"
The message appears for whatever is the first part of the formatting code that is trying to run, I just happened to make note of the above after I had been trying a couple of things, but once it's failed it then fails on all sections you try and run.
Any idea's on where I've gone wrong?
Code:
Sub FormatExport(Filename As String, Qry As String)
Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range
Set xlApp = CreateObject("excel.application")
Filename = Filename & ".xlsx"
Set xlWS = xlApp.Workbooks.Open(Filename).Sheets(Qry)
xlApp.Visible = True
'Make columns wide and then autofit, change font size.
Range("A1", Range("A1").End(xlToRight)).Select
With Selection
.ColumnWidth = 150
.Font.Size = 9
End With
Cells.Select
Cells.EntireRow.AutoFit
'Apply filter and set top row to bold
xlWS.Select
xlWS.Rows(1).Font.Bold = True
xlWS.Rows(1).AutoFilter
'Freeze Panes
xlWS.Activate
Range("A2").Select
xlWS.Activate
xlApp.ActiveWindow.FreezePanes = True
'Save and close
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
'Tidy up Excel
Set xlApp = Nothing
Set xlWS = Nothing
Set xlRng = Nothing
End Sub
Thanks