Want Macro to save to .xlsx

[John Yab]

I hope someone will help me with the code below, please. I want to save the
workbook to an .xlsx format so that the user can run the code from the macro
in the template workbook but save the results to a workbook without the macro
attached to it. I thought the code that is about 10 lines from the bottom
would do it but the result is getting saved as an .xlsm format.

Sub BMacro()

'This macro asks the user to navigate to a CSV file and then imports the CSV
file to the last sheet of
'this macro\template report. Then the data from the CSV is pasted to the
sheet: "Batch data". The macro
'then requests input from the user for the batch number that this report
applies to and then uses this
'input to re-title the 4 charts.


Dim file As Variant
Dim WSD As Worksheet ' csv data worksheet
Dim WSB As Worksheet ' Batch data worksheet

Set WSB = Worksheets("Batch data")

'Delete the sheet "CSV" if it exists
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("CSV").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a new worksheet and name it: "CSV".
Set WSD = ActiveWorkbook.Worksheets.Add(after:=Sheets("Plate vs Avg TT"))
WSD.Name = "CSV"

'ask the user to navigate to and select the appropriate CSV file
file = Application.GetOpenFilename("CSV Files (*.csv), *.csv",
Title:="Select a CSV File")

'if user presses cancel
If file = False Then GoTo Cancel

If file <> False Then

'import the CSV file to the last sheet of this workbook
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & file, Destination:=Range("A1"))
.Name = file
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End If

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Destination:=Sheets("Batch data").Cells(1, 1)
FinalRow = WSB.Cells(Rows.Count, 1).End(xlUp).Row
WSB.Range("J3:R3").AutoFill Destination:=WSB.Range("J3:R" & FinalRow)

'ask the user to type the batch number for this report and then use this
input to re-title the 4 charts
x = InputBox(Prompt:="Please type the batch number for this report in
the field below. (Example: 9NP20101)", _
Title:="Enter Batch Number")

'if user presses cancel
If x = vbNullString Then GoTo Cancel

Sheets("Batch chart").ChartTitle.Text = "NZBCA " & x
Sheets("Plate vs Vac Chart").ChartTitle.Text = "NZBCA " & x
Sheets("Avg TT vs Vac Chart").ChartTitle.Text = "NZBCA " & x
Sheets("Plate vs Avg TT").ChartTitle.Text = "NZBCA " & x

'Make sure the first chart is the active sheet
Charts("Batch chart").Activate

'Save the file
Filename = Application.GetSaveAsFilename(x & ".xlsx", FileFilter:="Excel
Files (*.xlsx), *.xlsx")
'if user presses cancel
If Filename = False Then GoTo Cancel
ActiveWorkbook.SaveAs Filename:=x

Cancel:
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Close False
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

 

[Barb Reinhardt]

In this line

ActiveWorkbook.SaveAs Filename:=x


x should be something like

C:\abc123.xlsx

You'll also need to add fileformat = 51 to that line.

 

[John Yab]

Hi Barb,

Very kind of you to reply, Thanks.

I figured it out. I changed the code to be:

'Save the file
Filename = Application.GetSaveAsFilename(x & ".xlsx", FileFilter:="Excel
Files (*.xlsx), *.xlsx")
'if user presses cancel
If Filename = False Then GoTo Cancel
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=x & ".xlsx",
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
On Error GoTo 0
Application.DisplayAlerts = True

and it now works. The template workbook contains the macro, does some stuff,
then saves the modified template to a non-macro workbook, then the template
workbook closes without saving the changes made to the template there by
preserving the template for the next use.