vba excel increment number value in cell on another workbook?

Question

I am trying to use vba to update a number value in a cell on another excel workbook by 1.

so if I have the value 466 in workbook 2 then when I run my code form workbook 1 this will update 466 to 467, and each time the code is run it gets incremented by 1.

the workbook will normally be closed but I want it to be able to work whether the workbook is open or closed, if that matters.

I am trying to use the following code but its not updating anything and I am not getting any errors.

Please can someone show me what I am doing wrong. Thanks

Dim ws1112 As Worksheet, ws2221 As Worksheet
    Set ws1112 = Sheets("Statistics")
    Set ws2221 = Workbooks("\\UKSH000-File06\Purchasing\New_Supplier_Set_Ups_&_Audits\Workbook 2.xls").Sheets("Dashboard")
    ws2221.Range("C7").Value = ws2221.Range("C7").Value + 1

What is the issue with your code? Is it not working, if yes, then what is the error? — Paresh J, Nov 26 '14 at 14:57
It will be hard to keep the changes when you modified the closed worbook if you don't save them ;) — Matteo NNZ, Nov 26 '14 at 15:03
I cannot try the code now but just try to add `ws2221.Save` right after your variable increase — Matteo NNZ, Nov 26 '14 at 15:04

Aiken · Accepted Answer · 2014-11-26T15:48:45.223

0

Now I'm going to go out on a limb and say that your error is "Error '9', subscript out of range" but that this error is somehow being suppressed or silently handled by the rest of your code (for example an On Error GoTo statement).

This is occurring because you can't open a workbook simply by including the full filepath in your Workbooks("<name>") statement. You'll need to use Workbooks.Open("<FilePath>") for that.

For example:

Dim wb As Workbook
Dim ws1112 As Worksheet
Dim ws2221 As Worksheet

Set ws1112 = Sheets("Statistics")

Set wb = Workbooks.Open("\\UKSH000-File06\Purchasing\New_Supplier_Set_Ups_&_Audits\Workbook 2.xls")
Set ws2221 = wb.Sheets("Dashboard")

ws2221.Range("C7").Value = ws2221.Range("C7").Value + 1

'Optional if you want to close the workbook afterwards
wb.Close SaveChanges:=True

edited Nov 26 '14 at 15:48

answered Nov 26 '14 at 15:41

Aiken

2,628
2
18
25

thanks for your help, I copied the code exactly and it still isn't incrementing the cell value in my workbook 2. I am not getting any error either. – kyle ridge Nov 26 '14 at 15:49
@kyleridge You might want to check that the code in question is actually being executed. I tested the above code with some dummy files on my own system and it worked just fine. The fact that no errors were displayed in your initial attempt suggests that the code is not actually being executed or that there's some suspect error handling going on, without seeing the rest of your code I can't say for sure. – Aiken Nov 26 '14 at 15:52
thanks I managed to get this working, it just seemed to be a case of where id placed the code, however although this now does what I need it to do, its opening the entire workbook as it updates the cell, is there a way to set the workbook to not visible so it does all this in the background? – kyle ridge Nov 26 '14 at 16:56
@kyleridge I suggest this [reading](http://stackoverflow.com/a/579846/3111149)... don't forget to mark the answer if it worked for you ;) – Matteo NNZ Nov 26 '14 at 18:09

vba excel increment number value in cell on another workbook?

1 Answers1