Using GetObject Excel 2010

Jason

How are you using GetObject?

Can you post the code?

Private Sub Tier1Check2()

Dim Tier3(1 To 34)
Dim i As Integer
Dim Cnt As Integer
Dim wkbObj As Workbook


For i = 1 To 34
   ' Fill the array with 34 values from column C of
   ' the worksheet.
   Tier3(i) = wkbObj.Worksheets(1) _
   .Range("C" & i + 1).Value
Next i


End Sub

I don't see GetObject in that code.

I also don't see where you are setting wkbObj to refer to, well, anything.

Is wkbObj supposed to refer to a workbook that's open in the same instance of Excel as the code is?

your code did not set wkbObj  ... you need to open something, or set it to a workbook that is already open, to reference its content ;) ~

Thanks Crystal, but when I copy your code into my VBA window I get a "Compile error: Syntax error"

Dim wkbObj As Excel.Workbook


set wkbObj = Workbooks.Open Filename:="C:\Temp\TiersLookup.xlsx"

Any ideas?

You need () around the arguments, eg Filename, you pass to the Open method.

Set wkbObj = Workbooks.Open (Filename:="C:\Temp\TiersLookup.xlsx")

Select allOpen in new window

PS If the problem was with a reference the error would be something like 'User defined type not declared' and it would be on the declaration of wkbObj.

thanks, Norie -- yes, I forgot the parentheses!

Fantastic that sorts the GetObject problem.

In my original posting I was looking for some assistance in confirming the logic that could be used to:

"My goal is to confirm that the information selected on one spreadsheet agrees with combinations on another. For example my reference data has information such as:-

 Value 1   Value 2  Value 3
 Car            Petrol     1600
 Car            Petrol     2000
 Van           Petrol     3000
 Van           Diesel     2000

 On the other spreadsheet a user may type Car, Petrol, 2000 or Car, Petrol, 20000 or Car, Petrol, 3000.

 I need to look at the reference data to confirm valid entries. I am trying to use the GetObject function in Excel to populate an Excel array from a further spreadsheet to do this. Can someone please confirm the VBA code I would need to perform this lookup and return a fail or pass?"

Can you help further please?

please post a sample of the 2 files to compare -- will be easier to write the code, thanks ~ Also, if you are already in Excel, you don't need GetObject, you already have Application

Assume: one workbook is open and the reference workbook may or may not be open.  In any case the path and name of the reference workbook needs to be known.

what is the purpose of this? to identify changes? for what reason?

thanks

Thanks again for your assistance Crystal.

In essence I want to ensure that values from a specific select list have been used by comparing the values on an alternative sheet of valid reference data. (I know you may say that a select list should be added to the original spreadsheet but sadly that isn't an option.

The reference data is held at 1 directory location e.g. c:\temp\refdata.xlsx (I'll call this one refdata.xlsx). Both spreadsheets are in Excel 2010.

The other spreadsheet which is used to populate a data load is the one that contains the data to compare. (I'll call this dataload.xlsx)

- I have added a button to dataload.xlsx
- This button will then call code to compare each relevant column\ combination of columns on Dataload.xlsx with refdata.xlsx to confirm that valid values have been used. (I say column\ combination of values as if you have car in refdata.xlsx column a then the only valid value for Column b is "petrol". Following on only specific values are relevant for column c on refdata.xlsx based on the combined values of column a and b.
- The same button needs to trim trailing and following spaces from any cells
- Check for some invalid characters such as an "=" symbol.

For any invalid values the cell should be coloured red.


dataload.xlsx

Sorry but can't upload sample files from my current location.