98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Export to Excel file
Export to Excel file
Resolved · Medium Priority · Version 2010
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
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Auto-insert the current timeIn Microsoft Excel, to enter the current time into a cell, hold CTRL+SHIFT and press SEMICOLON. |