Excel宏 - 导出工作表到csv并浏览到保存目录 [英] Excel Macro - Export sheet to csv and browse for save directory

问题描述

我成功地得到了一个宏观的工作，做我需要做的，但我想做得更好，不能。

I have succesfully got a macro working that does what I need it to do, but I would like to do it better, and can't.

这个位工作 -
通过单击excel中的按钮，用户导出一个具有动态文件名的csv的特定工作表，并将csv保存在预定目录中。

-- This bit works -- By clicking a button in excel, the user exports a specific sheet to a csv with a dynamic filename and it saves the csv in a pre-determined directory.

- 它可以做到这一点 -
而不是保存到一个预定的目录，我可以有浏览窗口显示，所以他们可以选择一个目录保存到？

-- Can it do this instead -- Instead of it saving to a predetermined directory, can i have the browse window show up so they can choose a directory for it to save to? I can't figure out how to do this.

这是我的宏：

Sub Export()
Dim MyPath As String
Dim MyFileName As String
MyPath = "C:\importtest"

MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy")

If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

Sheets("Export Data").Copy

With ActiveWorkbook

    .SaveAs Filename:= _
        MyPath & MyFileName, _
        FileFormat:=xlCSV, _
        CreateBackup:=False

    .Close False
End With
End Sub

推荐答案

正如Patrick所说，你正在寻找。 FileDialog 属性。

As Patrick suggested, you're looking for the .FileDialog property.

要实现它，请尝试：

Sub Export()
Dim MyPath As String
Dim MyFileName As String

MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy")

If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

Sheets("Export Data").Copy

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "" '<~~ The start folder path for the file picker.
    If .Show <> -1 Then GoTo NextCode
    MyPath = .SelectedItems(1) & "\"
End With

NextCode:

With ActiveWorkbook
    .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV,CreateBackup:=False
    .Close False
End With
End Sub

这篇关于Excel宏 - 导出工作表到csv并浏览到保存目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！