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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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,658
Messages
5,637,605
Members
416,976
Latest member
LL1300

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