Import Data - Receiving "extension don't match"

[Carin]

I'm attempting for the first time to get multiple files from a folder to append into one file. The data format is old "Microsoft Excel 97-2003 Worksheet." When just manually opening one file I receive "The file format and extension of 'file name.xls' don't match. The file could be corrupted or unsafe. Unless you trust it's source, don't open it. Do you want to open it anyway?" After opening if I try to change the file extension, the current format is "Text (Tab delimited)(*.txt)"
If I try to just simply change the extension, I receive an error while importing that the data is corrupt. If I open each file one-by-one and change the format and file extension, then I can import. Is there a faster way as I have >100 files to perform this task on a weekly basis. Thank you.

 

[Dossfm0q]

what about

Public Sub UpgradeXlsToXlsm()

Dim WB As Workbook
Dim XlFolder As Variant
Dim FldrPath As String
Dim XlExtension As String
Dim XlNewExtension1 As String, XlNewExtension2 As String
Set fso = CreateObject("scripting.filesystemobject")

XlFolder = Environ("USERPROFILE") & "\Desktop\MyFolder\" '<<<<<< Name your Folder Path
 Set XlFolder = fso.GetFolder(XlFolder)
For Each File In XlFolder.Files
N = InStr(1, File.Name, ".")
XlExtension = Mid(File.Name, N, Len(File.Name) - N + 1)
XlNewExtension1 = ".xlsx"
XlNewExtension2 = ".xlsm"
Filename = Mid(File.Name, 1, N - 1)

If XlExtension = ".xls" Then
Set WB = Workbooks.Open(File.Name)
With WB
'.SaveAs Filename:=XlFolder & "\" & Filename & XlNewExtension1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ''or
.SaveAs Filename:=XlFolder & "\" & Filename & XlNewExtension2, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
.Close True
End With
End If

Next

End Sub

 

[pjmorris]

Have you tried PowerQuery? Though it does seem as if the extension is incorrect and DossFMoQ's answer may well assist.

 

[Carin]

I received a compile error of Variable not defined for the "Set fso = CreateObject("scripting.filesystemobject")" line.

 

[KRice]

To the list of variables declared at the top of the code list, try adding this to them:
Dim fso As Object

 

[Carin]

I then received a compile error for the line "For Each File." So I added DIM File As Object. Now I get a compile error for "N = Instr" so I added DIM N as Object. Then getting compile error for "Filename =" so I added DIM Filename As Object. Now I get a path not found error next to Set XlFolder = fso.GetFolder. Not sure these are all Objects but it "appeared" to help me to get future through the macro.

I did enter my path:
XlFolder = Environ("USERPROFILE") & "C:\Users\FH8400\OneDrive - USPS\Carin's work spreadsheets\Bossio\Outlook Attachments" '<<<<<< Name your Folder Path
Set XlFolder = fso.GetFolder(XlFolder)

 

[KRice]

The InStr function will return an integer, so I think you'll want...
DIM N as Integer

And then for this line in the code...For Each File In XlFolder.Files
I think you'll want to declare...
DIM XlFolder as Object (rather than Variant)

For the Filename issue, you might just try...
DIM Filename
and see if that runs.

 

[Carin]

Where exactly would I put the Dim Filename?
Thank you

 

[KRice]

Where you have Dim Filename as Object...that actually might be fine. The variables/objects just need to be shown at the top before they are actually used, so there may not be any issue with what you have. But the compiler doesn't like the yellow highlighted line. I think concatenating USERPROFILE with the full path may be an issue. You might try, instead of...
XlFolder = Environ("USERPROFILE") & "C:\Users\FH8400\OneDrive - USPS\Carin's work spreadsheets\Bossio\Outlook Attachments"
this...
XlFolder = "C:\Users\FH8400\OneDrive - USPS\Carin's work spreadsheets\Bossio\Outlook Attachments\"
Note that I added a backslash at the end.

 

[Dossfm0q]

select your Folder press "Shift" Button Right Mouse copy "Path"

XlFolder = "then paste here\"

as KRice advice
XlFolder = "C:\Users\FH8400\OneDrive - USPS\Carin's work spreadsheets\Bossio\Outlook Attachments\"

Forget Environ("USERPROFILE")