Change file from .xlsx to .xlsm before using it?

[Mr2017]

Hi

I'm planning to use this code to import data from an .xlsx file.

But I was wondering if it's possible to save the .xlsx file as an xlsm file first (using VBA), after which I'll select a specific sheet in it....

Is that possible? ie use VBA to save the file as an .xlsm file first, then open that file?

Sub ImportFile()

Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook


Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
'fd.Filters.Add "xlsx files", "*.xlsx"
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"

If fd.Show <> -1 Then
   MsgBox "You didn't select a file?"
   Exit Sub
End If
'filewaschosen = fd.Show
fd.Execute

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:B" & lastrow).Copy
Report.Activate
ACV.Visible = True
ACV.Activate
Range("A1:B" & lastrow).PasteSpecial

End Sub

 

[Jeffrey Mahoney]

If you're only importing data from the file, why convert it?

You can rename the file, but Excel will tell you the format is not right. You can open it and immediately save as an .xlsm file.

A couple changes to get the SUB to work and to save the file as xlsm

Sub ImportFile()

Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook
Dim lastrow As Long
Dim ACV As Worksheet



Set Report = ThisWorkbook
Set ACV = Report.Worksheets("ACV")

Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
'fd.Filters.Add "xlsx files", "*.xlsx"
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"

If fd.Show <> -1 Then
   MsgBox "You didn't select a file?"
   Exit Sub
End If
'filewaschosen = fd.Show
fd.Execute
Set iWB = ActiveWorkbook

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:B" & lastrow).Copy
Report.Activate
ACV.Visible = True
ACV.Activate
Range("A1:B" & lastrow).PasteSpecial

iWB.SaveAs iWB.Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

 

[Mr2017]

Hi @Jeffrey Mahoney

Thanks for the prompt response.

I actually wanted to convert the file to an .xlsm then refer to certain sheet names using the back end.

But the code above gave me some other ideas which were useful.

Thanks you!