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

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?


VBA Code:
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,781
Members
416,940
Latest member
JohanT

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