Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course
Export to Excel file
Hi,
I have the following code that will export an excel file to a specific location as a PDF document. How should I change it so it saves it as an Excel document?
Const SAVE_PATH = "G:\Network Operations Department\Network Performance & Insight\2015\General Composite\Reporting\Performance Dashboards\July\"
Sub pdfProduce2()
Dim cell As Range
Dim wsSummary As Worksheet
Dim counter As Long
Set wsSummary = Sheets("Dashboard")
For Each cell In Worksheets("Dashboard").Range("U5:U34")
If cell.Value <> "" Then
'progress in status bar
counter = counter + 1
Application.StatusBar = "Processing file: " & counter & "/30"
With wsSummary
.Range("C3").Value = cell.Value
.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=SAVE_PATH & cell.Value & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End With
End If
Next cell
Set wsSummary = Nothing
End Sub
RE: Export to Excel file
Hi Mark
Tt doesn't seem possible to use ExportAsFixedFormat to create normal Excel workbooks as you can for creating pdf files.
However you can use saveAS in the usual way. Your code would read instead...
Sub XlsxProduce()
Dim cell As Range
Dim wsSummary As Worksheet
Dim counter As Long
Set wsSummary = Sheets("Dashboard")
For Each cell In Worksheets("Dashboard").Range("U5:U34")
If cell.Value <> "" Then
'progress in status bar
counter = counter + 1
Application.StatusBar = "Processing file: " & counter & "/30"
With wsSummary
.Range("C3").Value = cell.Value
.Select
.Copy
End With
ActiveWorkbook.SaveAs Filename:=SAVE_PATH & cell.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
End If
Next cell
Set wsSummary = Nothing
End Sub
Hope that helps?
Regards
Doug
Best STL
