Would like to convert excel formula to VBA code

[Carty]

Hi
In cell F25 on an excel spreadsheet called Other Ingot Stock I have the following formula:

='\\....\Stock List\VGF ingot stock\[All VGF Stock.xls]Total Length'!$G$7

which references a particular cell ($G$7) on a sheet called Total Length in the workbook All VGF Stock which is stored in the network address \\.... etc
I set the start value of a variable (A1) by refferring to this cell in the macro with this line of code:

If Sheets("Other Ingot Stock").Cells(6, 25) <> 0 Then A1 = (A1 + Sheets("Other Ingot Stock").Cells(6, 25))
In other words if F25 isn't blank then set variable A1 to the value in cell F25 and then the macro goes off and sorts by a specific code and adds the contents of a specific cell to A1. I would like to set the start value of A1 without referring to the formula in the cell . I have tried this:

A1 = Workbook("\\....\Stock List\VGF Stock").Worksheet("All VGF Stock").Cells("$G$7").Value
but it returns a compile error: sub or function not defined and the word Workbook is highlighted

 

[Andrew Poulsom]

Welcome to the Board.

It should be Workbooks, but only open workbooks are in that collection, not workbooks on disk. Maybe you can use this:

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

 

[Carty]

Thanks for the reply
I don't understand the caveat at the foot of the page in the link supplied ("in order for this function to work properly, a worksheet must be active in Excel. It will generate an error if all windows are hidden, or if the active sheet is a Chart sheet.")
Does this mean that the worksheet has to open?
Apologies if I am showing my ignorance, I'm new to VBA programming and only have a limiited excel formula knowledge - all I have for reference material is John Walkenbach's Excel VBA programming for dummies!

 

[Peter Thompson]

Cells("$G$7"). should be range("$g$7") or cells(7, 7)

Also change workbook to workbooks like Andrew said.

 

[Andrew Poulsom]

Thanks for the reply
I don't understand the caveat at the foot of the page in the link supplied ("in order for this function to work properly, a worksheet must be active in Excel. It will generate an error if all windows are hidden, or if the active sheet is a Chart sheet.")
Does this mean that the worksheet has to open?
Apologies if I am showing my ignorance, I'm new to VBA programming and only have a limiited excel formula knowledge - all I have for reference material is John Walkenbach's Excel VBA programming for dummies!

A worksheet has to be active. That can be in a workbook containing the code or some other workbook. The workbook whose value youe are getting doesn't need to be open.

 

[Carty]

Andrew - thanks that clears that up nicely. Using the top sub routine from the link supplied I substituted my network address information:
Sub TestGetValue()
p = "\\....\VGF Stock"
f = "All VGF Stock.xls"
s = "Total Length"
a = "G7"
MsgBox GetValue(p, f, s, a)
End Sub
When run it returns a compile error: sub or function not defined and the Msgbox line is highlighted. Is it possible that it is only good for files stored on my computer rather than a server?

Peter - thanks for your reply. When I make the corrections you suggest it returns a runtime error 9: Subscript out of range (I commented out the 'get value' sub) and it highlights the line
A1 = Workbooks("\\....\Stock List\VGF Stock").Worksheet("All VGF Stock").Range("$G$7").Value

 

[Peter Thompson]

You had worksheet rather than worksheets try the below.

A1 = Workbooks("\\....\Stock List\VGF Stock").Worksheets("All VGF Stock").Range("$G$7").Value

 

[Carty]

Peter
The same error is returned after the corrections. I think that it may be to do with the file's location. It's on a server not the hard drive of my computer but I am only guessing.
I think that I'll employ the old adage 'if it's not broken don't fix it!' I was just trying to make the macro tidier/more efficient
Thanks for your (and Andrew's) help
Carty

 

[ZVI]

Hi,
The recommendations:
1. Copy the code of GetValue() user-defined function to the module where your TestGetValue() is placed.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

' http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/<o:p></o:p>
Function GetValue(path, file, sheet, ref)<o:p></o:p>
 'Retrieves a value from a closed workbook<o:p></o:p>
 Dim arg As String<o:p></o:p>
 'Make sure the file exists<o:p></o:p>
 If Right(path, 1) <> "\" Then path = path & "\"<o:p></o:p>
 If Dir(path & file) = "" Then<o:p></o:p>
     GetValue = "File Not Found"<o:p></o:p>
     Exit Function<o:p></o:p>
 End If<o:p></o:p>
 'Create the argument<o:p></o:p>
 arg = "'" & path & "[" & file & "]" & sheet & "'!" & _<o:p></o:p>
   Range(ref).Range("A1").Address(, , xlR1C1)<o:p></o:p>
 'Execute an XLM macro<o:p></o:p>
 GetValue = ExecuteExcel4Macro(arg)<o:p></o:p>
End Function<o:p></o:p>

<o></o>
<o></o>
2. In TestGetValue() instead of p = "\\....\VGF Stock" write full path like this: p = "\\ServerName\Stock List\VGF Stock"
Don’t use "\\....\" in a path<o></o>
<o></o>
3. Try calling of TestGetValue() again

Regards,
Vladimir

 

[Carty]

Vladimir
Thanks for your response. I will try this tomorrow.
With regard to point 2, I use \\....\ to spare the blushes of my company (and for security too) . I write it in full when applying it to the code
Carty