runtime error 9 subscript out of range when activating a .csv file

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

This is a new one on me, I open several files as part of a batch, process data via a macro then save the resulting file. For months this has been working fine, no issues, but in the past few days I have been getting a runtime error 9 when it hits the following line in VBA. None of the coding has been changed at all, it has been static for months.

One clue? If I take the .csv file which is being activated, remove it from the folder it resides in then place it back in the folder then repeat the task by reopening all files then running the macro again it does not fail at the line below, otherwise I get the error. But this is time consuming. Have seen the issue with other files also - but again only very recently. All the files which fail on activation are .csv file and all actioned smoothly until the past few days.

The line it fails with subscript out of range is:

Windows("Website Suppliers.csv").Activate

Any help welcome.

Thanks, Steven
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try replacing Windows with Workbooks
 

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Try replacing Windows with Workbooks

Thanks for the very prompt reply Rory, no luck, I changed it to Workbooks and still got the runtime error.

Workbooks("Website Suppliers.csv").Activate

When it runs the debugger shows it back at Windows ("Website Suppliers.csv").Activate

As I say, the code has been working perfectly for months, so something has change beyond my VBA coding. Pull the file, put it back and it works!

-Steven
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In that case either something in your code is closing or renaming the workbook, or the workbook is actually open in a different instance of Excel.
 

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case either something in your code is closing or renaming the workbook, or the workbook is actually open in a different instance of Excel.

I do not even have to amend the file to get it to work, I just have to take Website Suppliers.csv out of the folder it resides in and put it back in the folder (do not even have to open it rename it or whatever, just take it out the folder and put it back in).

The folders are shared via Dropbox, but always have been.

Problem has only occurred since Friday evening, files have been processing perfectly for several months if not years prior. I've been using it 6 days a week, first error this year.

It is very early on in the VBA coding and nothing at all was changed with the code. There is no renaming of the file in any of my coding. The file is generated by gathering data from FTP sources via a previous process.

Something has changed beyond the VBA, I just wish I could work out what or amend my VBA for it to work now.

Totally mystifies me how something can change like this! :)

Thanks for trying, open to suggestions!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I suggest you post the code, since using Activate is rarely necessary anyway.
 

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Rory, code is fairly vast, the "Activate" tells it to carry out the actions following on the .csv file and not on the .xlsx which contains the VBA macro, so I do not see how it is not required.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you properly qualify the objects you are working with - eg you specify the workbook and worksheet for a range rather than just using Range - then there are very few instances where you ever need to activate or select anything in code.
 

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
If you properly qualify the objects you are working with - eg you specify the workbook and worksheet for a range rather than just using Range - then there are very few instances where you ever need to activate or select anything in code.

Hi Rory, I am no VBA wizard :) Cut a bit of the code below, the ranges etc. I want things to act on are in the .csv as I say, this worked perfectly, long term till last Friday. I do not know what changed. Working from home since I am on the sheltering list during lockdown, normally I would go to another PC and see if it had the same fx.

How do I specify the workbook and sheet other than Activate? The Activate on the .xlsm works perfectly still btw. it is the Activates to the .csv files that are having issues (but did not before last Friday).

Thanks, Steven

1594653769462.png
 

Attachments

  • 1594653683839.png
    1594653683839.png
    22.5 KB · Views: 1

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
When it debugs on that line, open the Immediate Window (press Ctrl+G if it's not already visible), paste this in and press enter:

Code:
for each wb in workbooks: debug.print wb.name: next

and tell me what output you see.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,538
Messages
5,636,901
Members
416,948
Latest member
Jkpang

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