VBA Code to look at a range in a different workbook

[Malcolm torishi]

Hi
I have two workbooks the first named "number" which just contains the number 50000 on sheet 1 cell A1. This "number" workbook is always closed and saved in c:\me\"number". The second named "sales" has a form control button with code as follows and is always opened and saved in c:\me\
Sub next invoice()
range ("A1"). Value= range ("A1"). value+1
End sub
When I press the button this code does not work because I am missing the vba code to find the close file can any one help please. Thank you

 

[VoG]

I think that you willneed something like Harlan Grove's Pull function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger

 

[Malcolm torishi]

Sorry that is new to me I do not understand would you be able to explain please

 

[VoG]

Getting data from closed workbooks is not straightforward. Why not just open the workbook and read in the value?

 

[Malcolm torishi]

Would it be possible if the work book was open at the same time. The reason this number is on a separate workbook is because this number will increase by one each time a user press the button thus creating a invoice number . So I am thinking if the sales workbook is shared between 3 users each time they press the button they will each get a different number. That's what I hope anyway.

 

[VoG]

Try something like

Sub next_invoice()
Dim wb As Workbook, x As Variant
Set wb = Workbooks.Open("c:\me\number.xls")
x = Range("A1").Value
wb.Close False
Range("A1").Value = x + 1
End Sub

 

[Malcolm torishi]

Hi I have just tried this with both workbooks open and it says "cannot run macro"sales.xlsm!macro" the macros may not be available in this workbook or all macros may be disabled

 

[VoG]

So where does sales.xlsm come into this? I am thinking that you have posted an apparently simple problem that is more complicated.

 

[Malcolm torishi]

This is my second workbook in the same folder which has my form control button on and were I have inserted your code. My pressing this button I would like the code to generate the next number from my "number" spreadsheet and with me linking this back to my sales spreadsheet the next number will appear when the button is pressed

 

[xenou]

It seems a bit heavy to use a workbook just store a number. I was thinking using a text file instead would be quicker and simpler. For example (lightly tested but seems to work):

[COLOR="Navy"]Function[/COLOR] nextInvoiceNumber() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] lastInvoiceNumber [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] iFile [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="SeaGreen"]'//increment and return next number from numbers.txt[/COLOR]
[COLOR="SeaGreen"]'Notes:[/COLOR]
[COLOR="SeaGreen"]'  ** If another user tries to open the file at the same time, a zero is returned.[/COLOR]
[COLOR="SeaGreen"]'     it is expected this would be a rare event as the file is opened only for milliseconds,[/COLOR]
[COLOR="SeaGreen"]'     but consider providing some way for the user to try again, or to make repeat attempts from the calling code.[/COLOR]
[COLOR="SeaGreen"]'  ** Numbers are stored as binary hex so you can't view them as plain text, but you could use a routine like this[/COLOR]
[COLOR="SeaGreen"]'     one to view or edit the next number, if needed.[/COLOR]
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler
    
    iFile = FreeFile
    [COLOR="Navy"]Open[/COLOR] "C:\myTemp\numbers.txt" [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Random[/COLOR] [COLOR="Navy"]Lock[/COLOR] [COLOR="Navy"]Read[/COLOR] [COLOR="Navy"]Write[/COLOR] [COLOR="Navy"]As[/COLOR] #iFile Len = 4

    [COLOR="Navy"]Get[/COLOR] #iFile, 1, lastInvoiceNumber
    nextInvoiceNumber = lastInvoiceNumber + 1
    [COLOR="Navy"]Put[/COLOR] #iFile, 1, nextInvoiceNumber
    
ErrHandler:
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Close[/COLOR] #iFile
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]