通过excel vba GUI将excel文件导出为txt格式 [英] Export excel file to txt format via excel vba GUI

问题描述

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

我有一个宏将Excel文件转换为txt格式

  Private Sub ConvertToText（）
 ActiveWorkbook.SaveAs FileName：=C：\Projects\ExelToText\Text.txt，FileFormat：= xlCurrentPlatformText，CreateBackup：= False 
 End Sub 
我的问题是如何将FileInput和FileOutput中的值作为变量传递给上述宏，而不是对文件路径进行编码。欣赏你的帮助，如果你有更好的建议，请分享。感谢
 
 
 
以下是完整的源代码
  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 =请选择一个Excel文件
 fdl.InitialFileName =c：\
 fdl.InitialView = msoFileDialogViewSmallIcons 
 
 fdl.Filters.Clear 
 fdl.Filters.AddExcel Files，* .xlsx; * .xls
 
 FileChosen = fdl.Show 
 
如果FileChosen<> -1然后
 
 MsgBox你没有选择
 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：=文本文件，* .txt，所有文件，*。*，_ 
标题：=另存为文件名）
 
 
如果file_name = False则退出Sub 
 
 
如果LCase $（Right $（file_name，4））< .txt然后
 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 
  
 
 
解决方案
所以你的子程序 ConvertToText 需要一个文件路径/字符串参数：
 私人Sub ConvertToText（sourcePath as String，destPath as String）
 Dim wb as Workbook 
设置wb = Workbooks.Open（sourcePath）
 wb.SaveAs FileName：= destPath，
 FileFormat： = xlCurrentPlatformText，CreateBackup：= False 
 wb.Close 
 End Sub 
  
然后，对 ExportButton 进行小修改，将此参数发送到 ConvertToText  sub：
  Private Sub ExportButton_Click（）
 On Error Res ume Next 
 ConvertToText Me.TextBox1.Value，Me.TextBox2.Value'修改它，以便它指向您的窗体上的TextBoxes 
如果Err.Number<> 0然后
 MsgBox无法转换文件，请确保输入了有效的文件，vbCritical 
结束如果
错误GoTo 0 
 End Sub 
  
 
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




I've a macro to convert excel file to txt format
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

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

解决方案
Make it so your subroutine ConvertToText requires a file path/string argument:
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 ExportButton to send this parameter to the ConvertToText sub:
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


                        
这篇关于通过excel vba GUI将excel文件导出为txt格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！