Using GetObject Excel 2010

Hi,

I am trying to use the GetObject function in Excel 2010 but keep getting an error saying "Object variable or With Block variable not set"

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?

Thanks in advance for your assistance.

Regards

Jason
jay_waughAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Jason

How are you using GetObject?

Can you post the code?
0
jay_waughAuthor Commented:
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
0
NorieVBA ExpertCommented:
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?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Dim xlApp As Excel.Application
dim wkbObj as excel.workbook

Set xlApp = GetObject(, "Excel.Application")
set wkbObj  = xlapp.Workbooks.Open Filename:="c:\folder\myfile.xlsx"

Open in new window


BUT, if you are running this code from Excel, you do not need xlApp.  You can simply do this:
set wkbObj  = Workbooks.Open Filename:="c:\folder\myfile.xlsx"

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ;) ~
0
jay_waughAuthor Commented:
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?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you need to reference the Microsoft Excel Object Library* if you are not running this from Excel.  And if you are, you need to save the workbook as a macro-enabled workbook.

* from the menu in Visual Basic: Tools, References -- scroll to the Microsoft Excel # Object Library and check it.

# will be a number corresponding to your version.
0
NorieVBA ExpertCommented:
You need () around the arguments, eg Filename, you pass to the Open method.
Set wkbObj = Workbooks.Open (Filename:="C:\Temp\TiersLookup.xlsx")

Open 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.
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks, Norie -- yes, I forgot the parentheses!
1
jay_waughAuthor Commented:
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?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
1
jay_waughAuthor Commented:
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
0
jay_waughAuthor Commented:
Sorry but can't upload sample files from my current location.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, jay ~

will you be able to upload a sample this weekend? The sample can just be something you put together, not using official data ~
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.