Excel VBA -打开所有.csv文件并使用单元格值保存为.xlsx

Question

我想:

1. 打开同一文件夹中所有的。csv文件。
2. 以。xlsx格式各保存一份副本。
3. ，使用。xlsx格式文件名作为范围("B1")。值从相应的。csv文件。

这是我目前为止的代码(不工作):

Dim MyFolderPath As String
MyFolderPath = Application.DefaultFilePath
myFolderPath = Application.DefaultFilePath
MyFileName = Dir(myFolderPath & "" & "*.csv")
While MyFileName <> ""
Set CSVWorkbook = Workbooks.Open(myFolderPath & "" & MyFileName)
CSVWorkbook.SaveCopyAs Filename:=myFolderPath & "" & CSVWorkbook.Sheets(1).Range("B1").Value & ".xlsx"
CSVWorkbook.Close SaveChanges:=False
Wend

谁能帮我找出这个问题?

多谢!

Answer 1

你好TropicalMagic…

试试这个

`Sub test()
Dim MyFolderPath As String
Dim CSVworkbook As Workbook
Dim MyfileName As Variant
Dim NewFilename As String
MyFolderPath = Application.DefaultFilePath

MyfileName = Dir(MyFolderPath & "" & "*.csv")
While MyfileName <> ""
Set CSVworkbook = Workbooks.Open(MyFolderPath & "" & MyfileName)
NewFilename = CSVworkbook.Sheets(1).Range("B1").Text
CSVworkbook.SaveCopyAs fileName:=MyFolderPath & "" & NewFilename & ".xlsx"
CSVworkbook.Close SaveChanges:=False
MyfileName = Dir
Wend
End Sub`

Answer 2

还有一点要提。确保在表格(1)"b1"的范围内。如果你在excel中打开一个空的CSV并在B1中放入一些东西，保存它并重新打开，你会看到B1中的文本将移动到A1，因为excel解释CSV的方式，并且在空的CSV电子表格中没有控件或分隔符。为了解决这个问题，在A1中添加一些东西。

Answer 3

感谢您的回复!我也找到了一个解决办法:

Dim MyFolderPath As String
Dim CSVworkbook As Workbook
Dim MyFileName As Variant
Dim NewFileName As String
MyFolderPath = Application.DefaultFilePath
MyFileName = Dir(MyFolderPath & "" & "*.csv")
While MyFileName <> ""
Set CSVworkbook = Workbooks.Open(MyFolderPath & "" & MyFileName)
NewFileName = CSVworkbook.Sheets(1).Range("B1").Text
CSVworkbook.SaveAs MyFolderPath & "" & NewFileName & ".xlsx", xlOpenXMLWorkbook
CSVworkbook.Close SaveChanges:=False
MyFileName = Dir
Wend

欢呼