Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting Data From Closed Workbook Using GetValue 1

Status
Not open for further replies.
mattygriff

mattygriff

Programmer
May 14, 2001
350
GB
I'm trying to retrieve some data from a closed worksheet and was hoping to use Walkenbach's GetValue function but it keeps returning #REF.
Code: 
Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String

'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If

'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function



Sub PopulatePBR()

    Dim arrPath() As String
    
    txtWhichMER = "Please select the Monthly Engineering Report" & vbCrLf _
    & "to be included in this Project Board Report"
    
    txtWhichPSC = "Please select the PSC Report to be" & vbCrLf _
    & "included in this Project Board Report"
    
    MERFilename = Application.GetOpenFilename _
        ("Excel Files (*.xls),*.xls", , _
        "Please select Monthly Engineering Report...", MultiSelect:=False)
    
    
    arrPath = Split(MERFilename, "\")
    
    numParts = UBound(arrPath)
    
    strFilename = arrPath(numParts)
    
    strPath = ""
    
    For t = 0 To numParts - 1
        strPath = strPath & arrPath(t) & "\"
    Next t
    
    p = strPath
    f = strFilename
    s = "Sheet1"
    a = "B2"
     
    Sheet1.Range("SCHEME_NAME").Value = GetValue(p, f, s, a)
      
    SavePBR
    
End Sub
The data I want is defeinitely in Cell B2 on Sheet 1 and the path and filename are correct. The only thing I can think of is that Cell B2 is a merged cell - would that cause any problem?

I have tried using a named range "SCHEME_TITLE" rather than the direct "B2" reference but that doesn't work either - I guess that's something to do with the "xlR1C1" definition?

Thanks in advance for any assistance!
 
Sort by date Sort by votes




Hi,

What as rhe ACTUAL argument values in your statement...
Code: 
Sheet1.Range("SCHEME_NAME").Value = GetValue(p, f, s, a)
cuz it works for me.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Sorry Skip, what's the question? Your typo (at least I assume it's a typo) has confused me a bit!

I'll assume for now that you want to know what the debugger returns for each of the parameters which are being passed to the GetValue function.

When I hover the cursor over the four parameters, I see what I would expect to see - i.e. p shows the path of the file, f shows the filename, and so on.

However, when I hover over the first part of the line I get
Code: 
Sheet1.Range("SCHEME_NAME").Value = Error 2023

In case it matters, I'm running Excel 2003.
 
Upvote 0 Downvote



Use the Watch Window and see what the OBJECT Sheet1.Range("SCHEME_NAME") returns. Do you actually have a NAMED RANGE on on the Sheet1 object, named SCHEME_NAME???

Also you have look at exactly what GetValue(p, f, s, a) returns with the Watch Window. HIGHLIGHT and select Add Watch.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Just a thought: If "Scheme_Name" is defined dynamically then it won't evaluate until the workbook is open.

Gavin
 
Upvote 0 Downvote
OK Skip, using the Watch Window I find that both
Code: 
Sheet1.Range("SCHEME_NAME").Value
and
Code: 
GetValue(p, f, s, a)
return Error 2023 as their values.

Any thoughts?
 
Upvote 0 Downvote



Which beggs the question, how many workbooks do you have open? Is Sheet1.Range("SCHEME_NAME") in the active workbook?

You may need to explicitly reference the workbook.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Code: 
Sheet1.Range("SCHEME_NAME")
is in the original workbook containing the macros.

The data I'm trying to retrieve via the
Code: 
GetValue(p, f, s, a)
function is in another unopened workbook.
 
Upvote 0 Downvote




If the Watch Window has ERRORS for BOTH, then there is something fundamentally wrong with 1) the range name and 2) one or more of your arguments is inconsistent.

Maybe strPath ends with a \, which it should not.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Thanks again Skip but no luck.

The range name I use is fine because after the code runs it contains the #REF generated by the GetValue function.

I have also modified my code to ensure that there is no trailing backslash at the end of strPath.

As a last attempt, here is my complete code.
Code: 
Sub STARTUP()
    numPBReportNo = Sheet1.Range("REPORT_NO").Value
    If numPBReportNo = 0 Then
        txtQuestion = "Do you want to create the first" & vbCrLf & "Project Board Report for this scheme?"
        msgFirstPBR = MsgBox(txtQuestion, vbYesNo + vbQuestion)
        If msgFirstPBR = vbNo Then
            Application.Quit
        Else
            FirstPBR
        End If
    Else
        txtQuestion = "Click OK to create a new Project Board Report" & vbCrLf & "or click Cancel to edit this Report"
        msgNewEditPBR = MsgBox(txtQuestion, vbOKCancel + vbQuestion)
        If msgNewEditPBR = vbOK Then
            NewPBR
        Else
            EditPBR
        End If
    End If
End Sub

Sub NewPBR()

    numPLRows = Sheet2.Range("PAYMENTS_LIST").Rows.Count
    
    numReportNo = Sheet1.Range("REPORT_NO") + 1
    
    Sheet1.Unprotect
    Sheet1.Range("REPORT_NO") = numReportNo
    Sheet1.Protect
    
    Sheet2.Unprotect
    
    Sheet2.Range("A" & 10 + numPLRows).Copy
    Sheet2.Range("A" & 10 + numPLRows).PasteSpecial (xlPasteValues)
    
    Sheet2.Range("PAYMENTS_LIST").Copy
    Sheet2.Range("PAYMENTS_LIST").PasteSpecial (xlPasteValues)

    
    Sheet3.Unprotect
    Sheet3.Range("CERTIFICATE_NO").Value = ""
    
    Sheet2.Range("A" & 11 + numPLRows).Formula = "=IF(CERTIFICATE_NO = 0,,CERTIFICATE_NO)"
    Sheet2.Range("B" & 11 + numPLRows).Formula = "=IF(A" & 11 + numPLRows & " > 0,TEXT(E" & 11 + numPLRows & ",""mmm-yyyy""),)"
    Sheet2.Range("C" & 11 + numPLRows).Formula = "=IF(A" & 11 + numPLRows & " > 0,NET_AMOUNT_DUE,)"
    Sheet2.Range("D" & 11 + numPLRows).Formula = "=IF(A" & 11 + numPLRows & " > 0,ROUND(C" & 11 + numPLRows & "*0.175,2),)"
    Sheet2.Range("E" & 11 + numPLRows).Formula = "=IF(A" & 11 + numPLRows & " > 0,PAYMENT_DUE_DATE,)"
    Sheet2.Range("F" & 11 + numPLRows).Formula = "=IF(A" & 11 + numPLRows & " > 0,C" & 11 + numPLRows & "+F" & 10 + numPLRows & ",)"
    Sheet2.Range("G" & 11 + numPLRows).Formula = "=IF(A" & 11 + numPLRows & " > 0,D" & 11 + numPLRows & "+G" & 10 + numPLRows & ",)"
    Sheet2.Protect
    
    numAmountDue = Sheet3.Range("AMOUNT_DUE").Value
    Sheet3.Range("PREVIOUSLY_CERTIFIED").Value = numAmountDue
    Sheet3.Protect
    
    Sheet4.Unprotect
    Sheet4.Range("PWDD").Copy
    Sheet4.Range("PREVIOUS_PWDD").PasteSpecial (xlPasteValues)
    Sheet4.Protect
    
    Sheet8.Unprotect
    Sheet8.Range("TOTAL_CLAUSE_60_1_1_CHANGES").Copy
    Sheet8.Range("PREVIOUS_TOTAL_CLAUSE_60_1_1_CHANGES").PasteSpecial (xlPasteValues)
    
    Sheet8.Range("PERIOD_CLAUSE_60_1_1_ADDS").Value = 0
    Sheet8.Range("PERIOD_CLAUSE_60_1_1_DDTS").Value = 0
    Sheet8.Protect
    
    Sheet9.Unprotect
    Sheet9.Range("TOTAL_CLAUSE_60_1_2_TO_19_CHANGES").Copy
    Sheet9.Range("PREVIOUS_TOTAL_CLAUSE_60_1_2_TO_19_CHANGES").PasteSpecial xlPasteValues
    
    Sheet9.Range("PERIOD_CLAUSE_60_1_2_TO_19_ADDS").Value = 0
    Sheet9.Range("PERIOD_CLAUSE_60_1_2_TO_19_DDTS").Value = 0
    Sheet9.Protect
    
    Sheet7.Unprotect
    Sheet7.Range("KPI_CURRENT").Copy
    Sheet7.Range("KPI_PREVIOUS").PasteSpecial (xlPasteValues)
    Sheet7.Range("KPI_CURRENT").Value = 0
    Sheet7.Protect
    
    Sheet15.Unprotect
    Sheet15.Range("FP_FIRST_MONTH").Value = "=IF(NOT(ISERROR(VLOOKUP(C10,PAYMENTS_LIST,2,FALSE))),VLOOKUP(C10,PAYMENTS_LIST,2,FALSE),0)"
    Sheet15.Range("FP_APRILS").Value = "=IF(NOT(ISERROR(VLOOKUP(C12,PAYMENTS_LIST,2,FALSE))),VLOOKUP(C12,PAYMENTS_LIST,2,FALSE),IF(N11>0,""Enter"",0))"
    Sheet15.Range("FP_MAY_TO_DEC").Value = "=IF(NOT(ISERROR(VLOOKUP(D10,PAYMENTS_LIST,2,FALSE))),VLOOKUP(D10,PAYMENTS_LIST,2,FALSE),IF(C11>0,""Enter"",0))"
    Sheet15.Range("FP_JAN_TO_MAR").Value = "=IF(NOT(ISERROR(VLOOKUP(L10,PAYMENTS_LIST,2,FALSE))),VLOOKUP(L10,PAYMENTS_LIST,2,FALSE),IF(K11>0,""Enter"",0))"
    Sheet15.Protect
    
    
    Sheet18.Unprotect
    Sheet18.Range("REALISED_RISKS_CURRENT").Copy
    Sheet18.Range("REALISED_RISKS_PREVIOUS").PasteSpecial (xlPasteValues)
    Sheet18.Range("PERIOD_RISKS_DETAILS").Value = ""
    Sheet18.Protect
        
    strPath = ThisWorkbook.path
    
    MEReportFilename = strPath & "/MonthlyEngineeringReportNo" & numReportNo & ".xls"
    
    ThisWorkbook.SaveAs (MEReportFilename)
    
    Sheet1.Activate
    Sheet1.Range("ASSESSMENT_DATE").Activate
    
End Sub

Sub FirstPBR()
    
    strPath = ThisWorkbook.path
    
    frmProjectDetails.Show
    
    numReportNo = Sheet1.Range("REPORT_NO") + 1
    
    Sheet1.Unprotect
'    Sheet1.Range("REPORT_NO") = numReportNo
'    Sheet1.Range("KEY_STAGE") = numKeyStage
'    Sheet1.Range("ECI") = txtECI
    Sheet1.Protect
    
    PopulatePBR
    
End Sub



Sub EditPBR()

End Sub

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String

'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If

'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function



Sub PopulatePBR()

    Dim arrPath() As String
    
    txtWhichMER = "Please select the Monthly Engineering Report" & vbCrLf _
    & "to be included in this Project Board Report"
    
    txtWhichPSC = "Please select the PSC Report to be" & vbCrLf _
    & "included in this Project Board Report"
    
    MERFilename = Application.GetOpenFilename _
        ("Excel Files (*.xls),*.xls", , _
        "Please select Monthly Engineering Report...", MultiSelect:=False)
    
    
    arrPath = Split(MERFilename, "\")
    
    numParts = UBound(arrPath)
    
    strFilename = arrPath(numParts)
    
    strPath = ""
    
    For t = 0 To numParts - 1
        strPath = strPath & arrPath(t)
        If t < numParts - 1 Then
            strPath = strPath & "\"
        End If
    Next t
    
    MsgBox strPath
    
    p = strPath
    f = strFilename
    s = "Sheet1"
    a = "B2"
     
    Sheet1.Range("C2").Value = GetValue(p, f, s, a)

'    MsgBox (GetValue(p, f, s, a))
    
    
    Stop
    
    SavePBR
    
End Sub


Sub SavePBR()

    PBReportFilename = strPath & "/PBRNo" & numReportNo & "_" & txtECI & "_KS" & numKeyStage & ".xls"
    
    ThisWorkbook.SaveAs (PBReportFilename)
    
    Sheet1.Activate
    Sheet1.Range("SCHEME_NAME").Activate
    
End Sub
This is all in a single Module with STARTUP being called via Workbook_Open.
 
Upvote 0 Downvote




Looking more closely at the function code, the PATH does need the trailing \. Sorry!

Argument f must be the workbook name, WITH the .xls file type.

There must be a Sheet1 tab in that workbook.

As I stated before, the function works, with the arguments I supplied.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Does the worksheet tab actually have to say "Sheet1"?

My tab actually says "Cover" - I thought the function used "Sheet1" as a literal reference in the manner of
Code: 
Sheet1.Range()
 
Upvote 0 Downvote



Cover is your sheet name. Sheet1 MAY be the SheetCodename, but it is NOT the name used by this Excel4 macro.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
OK, hopefully that will sort me out.

Can't test it right now - my 5 year old has commandeered the laptop and I'm stuck on this PC without Excel!!!

Thanks in advance, Skip.
 
Upvote 0 Downvote




Wooah! Your rugrat pulled rank, eh? They will probably know more about computers than the both of us combined!!! ;-)

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Well if she ever knows more than you then I reckon she'll make a million!!!

That last fix worked a treat - thanks again Skip.
 
Upvote 0 Downvote
Oh yeah - one other thing.

Rather than using the R1C1 cell reference such as
Code: 
a = "B2"
is there a way that I can use range names like
Code: 
a = "SCHEME_TITLE"
to make my code easier to follow?

I'm guessing it's something to do with the "xlR1C1" format but have no idea what changes I need to make.

Thanks in advance, as usual, for any help.
 
Upvote 0 Downvote




There is R1C1 notation, which I personally never use.

There is A1 notation, which is what you have been using.

There are Named Ranges, one or more cells in a range with a name designation, named using either the Name Box, Insert > Name > define, or Insert > Name > Create names in top/bottom row/left/right column.

Skip,

[glasses]Just traded in my old subtlety...
for a brand NUANCE![tongue]
 
Upvote 0 Downvote
Sorry Skip, I don't think I explained myself properly.

What I'm looking to do is to use Named Ranges to retrieve the data from the closed worksheet. Walkenbach's GetValue function code above requires the Reference (variable a) to be a column / row reference (B2 in my example) which I assume is tied to the "xlR1C1" parameter in the argument line
Code: 
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
There's no real reason why I can't use the column/row references (most if not all of the data I need to recover from the closed sheet will be in single cells).

I'd just prefer to recover data using the Named Ranges which are already created in the closed sheet to make my code a bit easier to read.
 
Upvote 0 Downvote
Oops, I've just made myself look a bit foolish there - that's what comes of trying to post about things you don't know much about!!!

I've just checked what R1C1 notation is and I can see why you never use them - looks terribly convoluted.

My question still remains though - is there any way I can retrieve data through an amended GetValue function using the Named Ranges already created in the closed worksheet?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
566
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
935
remeng
remeng
Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
861
strongm
strongm
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
722
strongm
strongm
renigar
  • Locked
  • Question
Fishing for Ideas on sorting sheets in workbook
Replies
5
Views
379
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top