vba Vba从文件夹中打开没有扩展名的文件并另存为不同的格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17318039/
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
Vba open files without extension from folder and save as different format
提问by Simone
I am trying write a macro to get every file in a folder to open and then save as an xlsx file. The files I am working with don't have a file extension, the data is tab delimited and opens fine manually. I have this code to open every file in a folder, however I haven't been able to get it to open files without a file extension.
我正在尝试编写一个宏来打开文件夹中的每个文件，然后另存为 xlsx 文件。我正在处理的文件没有文件扩展名，数据是制表符分隔的，可以手动打开。我有这个代码来打开文件夹中的每个文件，但是我无法让它打开没有文件扩展名的文件。
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "E:\Macro" 'location of files
ChDir sPath
sFil = Dir("*.xlsx") 'change or add formats
Do While sFil <> ""
Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file
'Code to save as different file extension would go here
oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
End Sub
I also don't know how to get each file to save as an xlsx file.
我也不知道如何让每个文件另存为 xlsx 文件。
I'm completely new to vba so any help would be greatly appreciated. Thanks
我对 vba 完全陌生，所以任何帮助将不胜感激。谢谢
回答by varocarbas
I understand that this is the code you are looking for:
我知道这是您正在寻找的代码：
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "E:\Macro" 'location of files
ChDrive "E" '-> if E is different than the current drive (if you didn't change it before, it is the drive where Office is installed)
ChDir sPath
sFil = Dir("*.*") 'change or add formats
Do While (sFil <> "" And InStr(sFil, ".") = 0)
NewFileName = sPath & "\" & sFil & ".xlsx"
On Error Resume Next
Name sFil As NewFileName 'Add extension to file
Set oWbk = Workbooks.Open(NewFileName) 'Open file as XLSX
'Do anything with the workbook
oWbk.Close True 'close the workbook, saving changes
sFil = Dir("*.*")
Loop ' End of LOOP
End Sub
回答by Andy G
Your current code is only looking for files that already have the .xlsx extension. Try Dir('*').
您当前的代码仅查找已具有 .xlsx 扩展名的文件。试试Dir('*')。
If you are just renaming the files then I wouldn't attempt to open them. I would use FileCopy
如果您只是重命名文件，那么我不会尝试打开它们。我会用FileCopy
FileCopy source, destination
where source and destination are both strings. For example, the following worked for me (on Windows XP):
其中源和目标都是字符串。例如，以下对我有用（在 Windows XP 上）：
FileCopy "testthis", "testthis.xlsx"
You could then use Killto delete the old file
然后您可以使用Kill删除旧文件
Kill pathname
