Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want Macro to save to .xlsx
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 -- John Yab |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want Macro to save to .xlsx
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. -- HTH, Barb Reinhardt "John Yab" wrote: 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 -- John Yab |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want Macro to save to .xlsx
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. -- John Yab "Barb Reinhardt" wrote: 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. -- HTH, Barb Reinhardt "John Yab" wrote: 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 -- John Yab |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save xlsx files | Excel Programming | |||
Unable to save XLSX files without using 'Save As' and different na | Setting up and Configuration of Excel | |||
How to default file save format to .xlsx in Excel? | Excel Discussion (Misc queries) | |||
Save Workbook As .xlsx to Remove Macros? | Excel Programming | |||
Can you convert an .xlsx and open/save as .xls in Excel 2003? | Excel Discussion (Misc queries) |