excel - 导出第 1 行中带有引号分隔符的文本文件的过程 - Excel
问题描述
我从 microsoft 支持网站LINK获得了以下代码
问题是它工作得有点太好了。我只需要带有“”限定符的列的第一行(基本上是表标题)。下面的代码将“”应用于所有单元格。
我不知道如何进行此更改。任何帮助将不胜感激!
非常感谢,OM
Sub QuoteCommaExport()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
DestFile = "C:\Users\Documents\Data\test.txt"
FileNum = FreeFile()
On Error Resume Next
Open DestFile For Output As #FileNum
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
On Error GoTo 0
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
If ColumnCount = Selection.Columns.Count Then
Print #FileNum,
Else
Print #FileNum, ",";
End If
Next ColumnCount
Next RowCount
Close #FileNum
End Sub
在电子表格中选择的数据如下所示:
Date Close Open High Low
24/04/2008 0.9399 0.9472 0.9484 0.9372
25/04/2008 0.9338 0.9394 0.9423 0.9289
28/04/2008 0.9382 0.9339 0.9405 0.9332
我需要的输出如下所示:
"Date","Close","Open","High","Low"
22/06/2015,21,20.698,21.019,20.575
23/06/2015,20.508,20.96,21.052,20.318
24/06/2015,20.679,20.475,20.709,20.287
基于@DisplayName 代码的输出
"Date","Open","High","Low","Close","Volume"
7/08/2015 , 3.84145514 , 4.80521243 , 3.4206597 , 3.76001086 , 164329
8/08/2015 , 3.78715895 , 3.800733 , 0.97017103 , 1.02256685 , 674188
9/08/2015 , 0.95851228 , 1.19425818 , 0.85406678 , 0.95275825 , 532170
是否可以删除逗号之间的空格?
解决方案
尝试这个:
Sub QuoteCommaExport()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
DestFile = "C:\Users\Documents\Data\test.txt"
FileNum = FreeFile()
On Error Resume Next
Open DestFile For Output As #FileNum
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
Goto CleanExit
End If
On Error GoTo 0
For ColumnCount = 1 To Selection.Columns.Count
Print #FileNum, """" & Selection.Cells(1, _
ColumnCount).Text & """";
If ColumnCount = Selection.Columns.Count Then
Print #FileNum,
Else
Print #FileNum, ",";
End If
Next
For RowCount = 2 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
If ColumnCount = Selection.Columns.Count Then
Print #FileNum, Selection.Cells(RowCount, ColumnCount)
Else
Print #FileNum, Selection.Cells(RowCount, ColumnCount); ",";
End If
Next ColumnCount
Next RowCount
CleanExit:
Close #FileNum
End Sub
推荐阅读
- ruby-on-rails - 使用地理位置而不是加/减小时显示时间
- jquery - Jquery-更改背景颜色和选择字段
- javascript - (谷歌图表)如何使堆积柱形图与小时、分钟和秒一起工作?
- javascript - Puppeteer 浏览器引用列表
- python - Python pandas 从 pandas 数据帧索引中删除每一天的第一分钟
- python - 名称:“碳水化合物”未定义
- python - 我对 tkinter 中的 .coords 有疑问
- javascript - 承诺关闭的 NodeJS UnhandledPromiseRejectionWarning
- pyspark - PySpark UDF 不返回预期结果
- python - 如何在烧瓶中以二进制格式获取http“GET”参数?