通过excel vba GUI将excel文件导出为txt格式

Export excel file to txt format via excel vba GUI

提问by user1902849

My goal is to export excel file to txt file format. The idea is to has a GUI to let user select the excel file that she/he wish to export and she/he can decide which file path and file name to save. Once users has finish input and output setting, he/she just need to click Export text button to export the excel file to txt file and save in the location that he/she has decided. The GUI as below

我的目标是将 excel 文件导出为 txt 文件格式。这个想法是有一个 GUI，让用户选择她/他希望导出的 excel 文件，她/他可以决定要保存的文件路径和文件名。用户完成输入输出设置后，只需点击导出文本按钮，即可将excel文件导出为txt文件，并保存在他/她决定的位置。图形用户界面如下

I've a macro to convert excel file to txt format

我有一个宏可以将excel文件转换为txt格式

Private Sub ConvertToText()
ActiveWorkbook.SaveAs FileName:="C:\Projects\ExelToText\Text.txt", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
End Sub

My question is how could I pass value from FileInput and FileOutput as variable to above macro instead harcode the filepath. Appreciate your helps and if you have any better suggestions, please share it out. Thanks

我的问题是如何将 FileInput 和 FileOutput 中的值作为变量传递给上述宏，而不是对文件路径进行硬编码。感谢您的帮助，如果您有更好的建议，请分享。谢谢

Below is the full source code

下面是完整的源代码

Private Sub ReadButton_Click()
OpenWorkbookUsingFileDialog
End Sub
------------------------------
Private Sub WriteButton_Click()
WriteWorkbookUsingFileDialog
End Sub
------------------------------
Private Sub ExportButton_Click()
ConvertToText
End Sub
------------------------------
Private Sub OpenWorkbookUsingFileDialog()

Dim fdl As FileDialog
Dim FileName As String
Dim FileChosen As Integer

Set fdl = Application.FileDialog(msoFileDialogFilePicker)

fdl.Title = "Please Select a Excel File"
fdl.InitialFileName = "c:\"
fdl.InitialView = msoFileDialogViewSmallIcons

fdl.Filters.Clear
fdl.Filters.Add "Excel Files", "*.xlsx; *.xls"

FileChosen = fdl.Show

If FileChosen <> -1 Then

MsgBox "You have choosen nothing"
ReadTextBox = Null
Else

 MsgBox fdl.SelectedItems(1)
 FileName = fdl.SelectedItems(1)
 ReadTextBox = FileName
End If

End Sub
-----------------------------------
Private Sub WriteWorkbookUsingFileDialog()

Dim file_name As Variant


file_name = Application.GetSaveAsFilename( _
    FileFilter:="Text Files,*.txt,All Files,*.*", _
    Title:="Save As File Name")


If file_name = False Then Exit Sub


If LCase$(Right$(file_name, 4)) <> ".txt" Then
    file_name = file_name & ".txt"
End If
WriteTextBox = file_name

End Sub
----------------------------
Private Sub ConvertToText()
ActiveWorkbook.SaveAs FileName:="C:\Projects\ExelToText\Text.txt",FileFormat:=xlCurrentPlatformText, CreateBackup:=False
End Sub

采纳答案by David Zemens

Make it so your subroutine ConvertToTextrequires a file path/string argument:

使您的子程序ConvertToText需要一个文件路径/字符串参数：

Private Sub ConvertToText(sourcePath as String, destPath as String)
    Dim wb as Workbook
    Set wb = Workbooks.Open(sourcePath)
    wb.SaveAs FileName:=destPath,       
    FileFormat:=xlCurrentPlatformText, CreateBackup:=False
    wb.Close
End Sub

Then, make small modifications to your ExportButtonto send this parameter to the ConvertToTextsub:

然后，对您进行小的修改以ExportButton将此参数发送到ConvertToText子：

Private Sub ExportButton_Click()
    On Error Resume Next
    ConvertToText Me.TextBox1.Value, Me.TextBox2.Value 'Modify this so that it refers to the TextBoxes on your form
    If Err.Number <> 0 Then 
        MsgBox "Unable to convert file. Please ensure a valid file was entered.", vbCritical
    End If
    On Error GoTo 0
End Sub