该文章的背景:
将配置文件写入数据库中 需要转成csv文件 xls 文件不能直接读取
参考链接 :
https://trumpexcel.com/vba-ranges/ 获取到当前可用的区域
Sub CopyCurrentRegion()
Range(“A1”, Range(“A1”).End(xlDown)).Copy Worksheets(“Sheet2”).Range(“A1”)
End Sub
将工作表转换为csv 文件链接
http://learnexcelmacro.com/wp/2017/09/save-excel-range-data-as-csv-file-through-excel-vba/#save-activeWorkSheet-as-CSV-file
Sub saveRangeToCSV()
Dim myCSVFileName As String
Dim myWB As Workbook
Dim tempWB As Workbook
Dim rngToSave As Range
Application.DisplayAlerts = False
On Error GoTo err
Set myWB = ThisWorkbook
myCSVFileName = myWB.Path & "\" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"
Set rngToSave = Range("C3:H50")
rngToSave.Copy
Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial xlPasteValues
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = True
End Sub
改过的实现代码
Application.DisplayAlerts = False
Dim myWB As Workbook
Set myWB = ThisWorkbook
Dim rngToSave As Range
Dim mySheet As Worksheet
Set mySheet = ActiveSheet
'选中从A1到最大列的最大行的最后一个
Set rngToSave = Range("A1", Range("A1").End(xlDown).End(xlToRight))
'复制一份
rngToSave.copy
Dim tempWB As Workbook
Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial xlPasteValues
.SaveAs Filename:=myWB.Path & "\" & mySheet.Name, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = True