Macro doesn't run anymore

Bouchg

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I ran a macro that will open other workbooks, copy and paste certain information from the other workbook and paste the Master Workbook. There are 50+ workbooks, so it works on a loop. It worked fine.

I had to restart my computer and when I went to rerun the macro on a different folder, the macro doesn't run anymore. No error message, it just won't the run.

What's troubling is that when I create a second module in the Master Workbook and get to open 1 workbook from the new file, the workbook opens. So I know it's not a filepath issue or a connectivity issue (other workbooks are saved on a server). It also tells me macros aren't disabled.

How can I get the macro to run again? I still got 3 more folders to go, each with 50+ workbooks to copy the information from.

Thanks for any help on this.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,270
Office Version
  1. 365
Platform
  1. Windows
How are you running the code?

Manually? Via a button?
 

Bouchg

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Manually. But now I've realized something. It is running and executing the code.

The loop only executes when the variable strExtension<>""

This is the code causing the issue:

ChDir strPath
strExtension = Dir("*.xlsm*")
Do While strExtension <> ""

strpath is defined earlier and is populating correctly based on the debugger. strExtension is then supposed to find all the macro-enable Excel files. For some reason, the code seems to think that strExtension=""

How do I get strExtension<>""?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,447
Office Version
  1. 365
Platform
  1. Windows
What exactly is strPath?
 

Bouchg

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Const strPath as String = whatever the file path is, which I've tested and know is valid.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,604
Office Version
  1. 365
Platform
  1. Windows
Shot in the dark here, but I think I may have seen something like this the other day, and the I think the issue was that the setting was change to hide extensions on files.
If you view the directory in Windows Explorer, does it show the file extentions, or are they hidden?
See here for how to show them, by default: How to Make Windows Show File Extensions
See if that makes any difference.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,447
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

whatever the file path is,
Yes but was is it? UNC, IP or mapped drive? If the latter what drive?
 

Bouchg

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Shot in the dark here, but I think I may have seen something like this the other day, and the I think the issue was that the setting was change to hide extensions on files.
If you view the directory in Windows Explorer, does it show the file extentions, or are they hidden?
See here for how to show them, by default: How to Make Windows Show File Extensions
See if that makes any difference.
Double checked - I can see them by default. But I toggled it on and off again and still not working.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,604
Office Version
  1. 365
Platform
  1. Windows
Double checked - I can see them by default. But I toggled it on and off again and still not working.
Sorry that didn't solve it. Like I said, it was a shot in the dark.

Be sure to answer Fluff's questions. He is pretty good at getting to the heart of the matter.
 

Bouchg

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Yes but was is it? UNC, IP or mapped drive? If the latter what drive?
Mapped Drive. I then realized that I'm connected to this shared drive via VPN, which has caused me issues with other applications in the past. I just moved the folders off the shared drive and onto a local drive. I changed the path to the local drive and now everything works fine. I don't know why the macro worked the first time via the VPN and then it stopped. But now it's fine.

Thanks for all the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,398
Messages
5,624,500
Members
416,031
Latest member
Omega67

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