VBA Excel文件到CSV，保持CSV文件名与原始工作簿相同 [英] VBA Excel file to CSV, keeps CSV filename same as original workbook

问题描述

我试图找到一个快速的方法来保存我的 xlsx 文件作为 csv 名称为 xlsx 文件（仅在 csv 格式）。我已经记录了一个宏的快捷方式，但问题是，每当我尝试一个新的文件，它保存为一个相同的文件名我记录初始宏（即见下面，可能是因为我有文件标记为： 3WDL_1（2014-08-07）10secDataTable sit.csv ）。是否需要将 3WDL_1（2014-08-07）10secDataTable sit.csv 替换为使用与实际工作簿相同的文件名保存的宏使用。

I am trying to find a fast way to save my xlsx files as csv files with the same file-name as the xlsx file (just in csv format). I have recorded a macro with shortcut, But the issue is that whenever I try with a new file it saves as a the same file-name I recorded initial macro with (i.e. see below, probably because I have the file labelled in code as: 3WDL_1 (2014-08-07)10secDataTable sit.csv). Is there something I need to replace 3WDL_1 (2014-08-07)10secDataTable sit.csv with to make the macro save with the same file-name as the actual workbook I am working with.

所以基本上我有一个文件夹充满 xlsx 文件，我想使用快捷方式/宏在每个 xslx 文件中将它们转换为与原始名称完全相同的 csv 文件 xlsx 文件，并保存在同一个文件夹中。

So basically I have a folder full of xlsx files and I want to use a shortcut/macro on each xslx file to convert them into a csv files that have exactly the same name as original xlsx file, and are saved into the same folder.

Sub xlstocsv()
'
' xlstocsv Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Columns("A:A").Select
    Range("A41243").Activate
    Selection.NumberFormat = "0.00"
    ActiveWindow.ScrollRow = 41231
    ActiveWindow.ScrollRow = 41090
    ActiveWindow.ScrollRow = 39753
    ActiveWindow.ScrollRow = 30184
    ActiveWindow.ScrollRow = 26385
    ActiveWindow.ScrollRow = 13017
    ActiveWindow.ScrollRow = 10976
    ActiveWindow.ScrollRow = 8162
    ActiveWindow.ScrollRow = 4785
    ActiveWindow.ScrollRow = 4503
    ActiveWindow.ScrollRow = 774
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    ChDir "C:\Users\paddy\Desktop\NEW CSV files whole CGM date ok!"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\paddy\Desktop\NEW CSV files whole CGM date ok!\3WDL_1 (2014-08-07)10secDataTable sit.csv" _
        , FileFormat:=xlCSVMac, CreateBackup:=False
End Sub

推荐答案

现在，你已经在 ActiveWorkbook.SaveAs 之后硬编码文件名，与硬编码的名称。

Right now, you've got the file-name hard-coded in after "ActiveWorkbook.SaveAs" so it's saving everything with that hard-coded name.

我想你会想要使用 ActiveWorkbook.Name 获取当前文件的名称，将它连接到您在那里（没有文件扩展名）的文件名变量与新的扩展名。例如：

I think you'll want to use "ActiveWorkbook.Name" to get the name of the current file and concatenate it into the "Filename" variable that you have there (without the file extension) with the new extension. For example:

"C:\Users\padd\Desktop\NEW CSV...ok!\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & ".csv")

这是一种肮脏的方式，你的需要。此外，根据您使用的Excel版本，我认为您可能需要使用 ThisWorkbook 而不是 ActiveWorkbook 但我不确定。

This is a kind of a dirty way to do it, but it should serve your needs. Also, depending on which version of Excel you use, I think you might need to use "ThisWorkbook" instead of "ActiveWorkbook" but I'm not sure.

这篇关于VBA Excel文件到CSV，保持CSV文件名与原始工作簿相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！