export excel file

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Export to Excel file

Export to Excel file

resolvedResolved · 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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Auto-insert the current time

In Microsoft Excel, to enter the current time into a cell, hold CTRL+SHIFT and press SEMICOLON.

View all Excel hints and tips


Server loaded in 0.04 secs.