我的目标是将excel文件导出为txt文件格式。这个想法是有一个 GUI 让用户选择她/他想要导出的 Excel 文件,并且她/他可以决定保存哪个文件路径和文件名。用户完成输入输出设置后,只需点击导出文本按钮即可将excel文件导出为txt文件并保存在他/她指定的位置。图形用户界面如下
我有一个宏可以将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 = "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
让它成为你的子程序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
然后,对您的ExportButton
将此参数发送到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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)