如何使用vba保存分号分隔的csv文件？ [英] How to save semi-colon delimited csv file using vba?

问题描述

最近我需要将一些数据复制到电子表格中，使用VBA进行格式化，然后将其保存在CSV文件中。我使用以下代码：

  ws.SaveAs文件名：= filestr，Fileformat：= xlCSV 
  

ws是我保存的工作表。

这将给我一个逗号-delimited CSV文件。

但是，我想将该表格保存为分号分隔的文件。我已经做了一些谷歌搜索，并找到以下解决方案：

1. 转到开始>设置>区域和语言选项


2. 单击自定义按钮


3. 在分号（;）中列出分隔符类型旁边

我已经按照上面的步骤，并将我的代码更改为：

  ws.SaveAs文件名：= filestr， Fileformat：= xlCSV，Local：= True 
  

但是我仍然有一个逗号分隔的CSV文件作为输出

我使用的excel版本是Excel 2003，而我的操作系统是Windows XP

有人可以建议如何使用VBA保存分号分隔文件？

解决方案

我刚刚检查过，因为有同样的问题。文件名在这种情况下没有功能。

这对我有用：

 使用ActiveWorkbook 
 .SaveAs文件名：=My File.csv，FileFormat：= xlCSV，Local：= True 
 .Close False 
 End with 
  

在区域设置 - >; < - 作为列表分隔符。关闭时也不要保存更改 - >关闭您必须使用 False 。

Recently I needed to copy some data into a spreadsheet, use VBA to format it, then save that sheet into a CSV file. I use the following code:

ws.SaveAs Filename:=filestr, Fileformat:=xlCSV

ws is the worksheet that I saved.

This will give me a comma-delimited CSV file.

However, I would like to save that sheet into a semicolon-delimited file. I have done some Googling and found the following solution:

1. Go to Start>Settings>Regional And Language Options
2. Click on the Customize button
3. Next to List Separator type in a semi-colon (;)

I have followed the procedures above and changed my code to:

ws.SaveAs Filename:=filestr, Fileformat:=xlCSV, Local:=True

But I still got a comma-delimited CSV file as output.

The version of excel I am using is Excel 2003 and my OS is Windows XP

Could anyone suggest how to save a semi-colon delimited file using VBA?

解决方案

i've just checked this because had same problem. Filename has no functionality in this case.

This is what worked for me:

With ActiveWorkbook
    .SaveAs Filename:="My File.csv", FileFormat:=xlCSV, Local:=True
    .Close False
End With

In regional settings -> ; <- as list separator. It is also important not to save changes when closing -> with Close you have to use False.

这篇关于如何使用vba保存分号分隔的csv文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！