VBA使用保存对话框创建具有文件名的新工作表并另存为CSV格式 [英] VBA to create new sheet with file name using save dialogue box and save as CSV format

问题描述

我的要求是我要准备一张包含一些数据的表和一个必须在上述任务下执行的按钮.

My requirement is that I am having a sheet with some data and a button which has to perform below said task.

任务是，一旦单击按钮，它就要求将文件名和位置以csv格式保存到新工作表中，并且必须将活动工作表中的数据复制并粘贴到新工作表中，并将新的文件名粘贴到新工作表中工作表应为文件以CSV格式保存的名称.

Task is that once button is clicked it has to ask for file name and location to be save to the new sheet in csv format and the data in the active sheet has to be copied and pasted in a new sheet and name of new sheet should be the name given for file to save in CSV format.

有人可以帮忙吗?

推荐答案

这应该可以解决问题，进行编辑以使其具有品味.将宏保存到新模块，然后添加按钮Developer Tab>插入>按钮.将按钮分配给该宏.

This should do the trick, edit to taste. Save the macro to a new module, then add a button Developer Tab > Insert > Button. Assign the button to this macro.

Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

'Change the path, must end with \
SaveToDirectory = "C:\Users\username\Documents\test\"
For Each WS In ThisWorkbook.Worksheets
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
Next
'edit/remove the for loop to suit
End Sub

应归还贷项的贷方.从此答案改编而成: 将Excel工作表保存为包含filename + worksheet的CSV文件使用VB命名

Credit where credit is due. Adapted from this answer: Saving excel worksheet to CSV files with filename+worksheet name using VB

这篇关于VBA使用保存对话框创建具有文件名的新工作表并另存为CSV格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！