Import Data - Receiving "extension don't match"

Carin

Board Regular
Joined
Feb 4, 2006
Messages
224
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
what about
VBA Code:
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
Have you tried PowerQuery? Though it does seem as if the extension is incorrect and DossFMoQ's answer may well assist.
 

Carin

Board Regular
Joined
Feb 4, 2006
Messages
224
I received a compile error of Variable not defined for the "Set fso = CreateObject("scripting.filesystemobject")" line.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Carin

Board Regular
Joined
Feb 4, 2006
Messages
224
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 4, 2006
Messages
224
Where exactly would I put the Dim Filename?
Thank you


1612307405467.png


1612307375466.png
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
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

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,285
Messages
5,635,321
Members
416,852
Latest member
kanaikls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top