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

[StevenGu3]

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

 

[RoryA]

Try replacing Windows with Workbooks

 

[StevenGu3]

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]

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]

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]

I suggest you post the code, since using Activate is rarely necessary anyway.

 

[StevenGu3]

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]

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]

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

 

[RoryA]

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:

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

and tell me what output you see.