我从谷歌上进行了一些研究
Sub ExportRangetoFile()
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
Set WorkRng = Sheets("Match").Range("K:K")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
wb.SaveAs Filename:="C:\Users\xxxx\Desktop\newcurl.bat", FileFormat:= _
xlTextPrinter, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
但是以 bat 格式显示的结果过大。因为该列上的每个单元格包含 295 个字符长(字符串)。
因此,部分结尾显示在批处理文件的底部,导致该批处理无法运行。
有什么想法或解决方法吗?
例子。
在一个单元格中 (excel)
卷曲 abcd....ef
curl ghij....kl
在.bat中
卷曲 abcd....
卷曲 ghij....
有效
吉隆坡
答案1
我认为你的问题的意思是:
我希望循环遍历 K 列中的每个单元格,并将每个单元格的内容保存到单个批处理文件中。
尝试以下 VBA,显然指定您自己的输出路径:
Sub ExportRangetoFile()
Dim ColumnNum: ColumnNum = 11 ' Column K
Dim RowNum: RowNum = 1 ' Row to start on
Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Users\Jonno\Documents\test\newcurl.bat") 'Output Path
Dim OutputString: OutputString = ""
Do
OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine
RowNum = RowNum + 1
Loop Until IsEmpty(Cells(RowNum, ColumnNum))
objFile.Write (OutputString)
Set objFile = Nothing
Set objFSO = Nothing
End Sub
或者如果您的工作表包含空行:
Sub ExportRangetoFile()
Dim ColumnNum: ColumnNum = 11 ' Column K
Dim RowNum: RowNum = 0
Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Users\Jonno\Documents\test\newcurl.bat") 'Output Path
Dim OutputString: OutputString = ""
Dim LastRow: LastRow = Application.ActiveSheet.Cells(Application.ActiveSheet.Rows.Count, ColumnNum).End(xlUp).Row
Do
nextloop:
RowNum = RowNum + 1
If (IsEmpty(Cells(RowNum, ColumnNum).Value)) Then
GoTo nextloop:
End If
OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine
Loop Until RowNum = LastRow
objFile.Write (OutputString)
Set objFile = Nothing
Set objFSO = Nothing
End Sub