Excel 2013 macro enabled workbook not run on 2016 version

[PRADEEPB270]

There are 48 files which have created in 2013 excel version in macro enabled workbook.

Now I have shifted from 2013 to 2016 version.All files macro are not running in 2016.

Should I go back to 2013 version ?

[HansV]

Excel 2016 isn't very different from Excel 2013, so macros should - in theory - run.

Do you get an error message? If so, what does it say?

[StuartR]

Are the Macros signed?
Are the Macros in a trusted location (check Options > Trust Center > Trust center settings > Trusted Locations)
What setting do you have in Options > Trust Center > Trust center settings > Macro Settings

Trust center settings.png

(This screenshot from Excel 2019)

[PRADEEPB270]

When I press ALT+F8 then The code Appears on the screen and do not work.Is there any setting change required ?

Please mention step by step

[HansV]

I recommend making the folder or folders containing the workbooks a trusted location for Excel:
- Select File > Options.
- Select 'Trust Center'.
- Click the 'Trust Center Settings...' button.
- Select 'Trusted Locations'.

485762_image0.jpg

- If you want to make a folder on a network share a trusted location, tick the check box 'Allow Trusted Locations on my network (not recommended)'.
- Click 'Add new location...'.
- Click 'Browse...'.
- Select the folder, then click OK.
- If you want to make all subfolders of the selected folder a trusted location to, tick the check box 'Subfolders of this location are also trusted'.
- Click OK.

S3032.png

- Now select 'Macro Settings'.
- Select 'Disable all macros with notification'.
- Click OK.

[PRADEEPB270]

please check this attachment.only cost break-up macro is not working.

In 2013 all the macros are working but in excel 2016 some of the macro are not working.like this " Cost break-up" in this attachment.

[HansV]

The VBA project is locked with a password, so I cannot investigate it.

[PRADEEPB270]

Sorry I have forget to remove the password.

Now please check

[HansV]

I have Excel 2019. The Costbreakup macro works correctly: it populates cells in columns K to O.

[PRADEEPB270]

But on my laptop with excel version 2016 and windows 10 pro ,it is not running.

What will you suggest ?

[HansV]

Have you checked the Trust Center Settings, as suggested above?

[PRADEEPB270]

Yes,all the macros are working except this i.e.cost break-up .

setting are ok.

[HansV]

Try the following:
- Comment out the following line by inserting an apostrophe ' at the beginning:

Code: Select all

   On Error GoTo lbl_err

- Run the Costbreakup macro.
- If you get an error message, please note what it says.
- Click Debug, and note which line is highlighted.

[PRADEEPB270]

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myWb.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

[HansV]

What was the error message?

[PRADEEPB270]

error:provider cannot be found.It may not be properly installed.

[HansV]

Does it work if you change

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myWb.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

to

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & myWb.FullName & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

[PRADEEPB270]

Yes,now it is working well.

Thanks HansV for understanding my problem and provide best solution.Awesome help.