vba 如何在vba excel中打开带有可变扩展名的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15686071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to open a file with a variable extension in vba excel
提问by Jonathan Raul Tapia Lopez
I have to made a vba for open a CSV from excel the problem is the files has a format:
我必须制作一个 vba 才能从 excel 打开 CSV 问题是文件有一种格式：
constan_name_file . YYY
constan_name_file 。YYYY
For example
对于例如
file1.124514 (Day 1)
file1.124514（第一天）
file1.144521 (Day 2)
file1.144521（第 2 天）
file1.152132 (Another day)
file1.152132（改天）
The name file is a constan but the YYY is variable, but the file inside is a CSV. thats possible to open it?
名称文件是一个常量，但 YYY 是可变的，但里面的文件是一个 CSV。那可以打开吗？
回答by Hartmut Blau
I've had a problem to keep the formatting when opening a csv-file in Excel 2007. Looked around in excel-forums only to find one possible solution. Change file-extension from csv to txt.
在 Excel 2007 中打开 csv 文件时，我遇到了保留格式的问题。在 excel 论坛中环顾四周只是为了找到一种可能的解决方案。将文件扩展名从 csv 更改为 txt。
Tried to make a Function as general as possible. Here it is:
试图使函数尽可能通用。这里是：
Option Explicit
Function ChangeFileExt(filnamn As String, extensionOld, extensionNew)
Dim oldname, newname as string
oldname = ThisWorkbook.Path & "\" & filnamn & "." & extensionOld
newname = ThisWorkbook.Path & "\" & filnamn & "." & extensionNew
Name oldname As newname
End Function
'...............................................................................
Sub change_extension()
' Objective: Want to keep csv-format at workbook.open
' Rename file from filename.csv to filename.txt
' Open txt-file
' process data
' Rename back to csv-file if neccesary
Dim csv, txt As String
csv = "csv"
txt = "txt"
Call ChangeFileExt("file_name_to_change", csv, txt) ' omit extension in file_name_to_change
...all kinds of code
Call ChangeFileName("file_name_to_change", txt, csv) ' change filename back to original name
End Sub
回答by Potter Rafed
Why dont you forcefully open it as a CSV file
为什么不强行将其作为 CSV 文件打开
Workbooks.Open Filename:=name, Format:=2
Here the 2 specifies that the file is comma delimited http://msdn.microsoft.com/en-us/library/office/ff194819.aspx
这里的 2 指定文件以逗号分隔 http://msdn.microsoft.com/en-us/library/office/ff194819.aspx
回答by Our Man in Bananas
Yes, try the below code:
是的，试试下面的代码：
dim sFileName as string
dim sExtension as string
sExtension="YYY"
sfilename=constan_name_file & "." & sExtension
workbooks.open sfilename, Format:=2
回答by Jonathan Raul Tapia Lopez
Thanks for the use of Wildcards, finally I get the code.
感谢使用通配符，终于拿到代码了。
sub GetFiles(direc As String, fich As String)
Dim strFileName As Variant
strFileName = dir(direc & "\" & fich & ".******")
If Len(strFileName) > 0 Then
'open
end if
End sub
