11 Replies - 1187 Views - Last Post: 24 April 2013 - 09:20 AM Rate Topic: -----

#1 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Get a cell value from excel range which is formatted as table in vb

Posted 23 April 2013 - 08:25 AM

Hi everyone, hope you all fine.
I have an excel sheet with some data that is formatted as table with titles for columns, like the attached image
I want to get a specific cell value (lets say the highlighted cell with value 50) using the column title.
I used many combinations of codes but some of them did not work at all and some others gave me the error "Application-defined or object-defined error"
Dim XLApp as Object
Dim XLBook as Object
Dim XLSheet as Object
Dim Value as Integer
Set SLApp = CreateObject("Excel.Application")
set XLBook = XLApp.Workbooks.Opem(FilePath)
Set XLSheet = XLBook.Worksheets(1)


I used the following lines of code to get the value
Value = XLSheet.Range(4)("Item Price")

Value = XLSheet.Range(4,"Item Price")

Value = XLSheet.Cells(4)("Item Price")

Value = XLSheet.Cells(4,"Item Price")



your early response is highly appreciated.
Thanks in advance.

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Get a cell value from excel range which is formatted as table in vb

#2 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 23 April 2013 - 08:32 AM

Correction
The SLApp in the code "line 5" is just a misspell :whistling:
It is XLApp :bigsmile:
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3817
  • View blog
  • Posts: 13,532
  • Joined: 12-December 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 23 April 2013 - 08:39 AM

If you want to use "Item Price" to help retrieve a value then you need to create a Defined Name based on the tables column-headings. The following code demonstrates. Notice that the space between the works "Item Price" is converted to an underscore.

    Range("A1").CurrentRegion.CreateNames True, False, False, False
    Value = Range("Item_Price").Cells(3).Value

Also, the range "Item_Range" starts at the cell below the heading.

If you run this code again it will prompt you to over-write all the defined names. You could turn off the display alerts temporarily so that you don't have to do this each time.

    Application.DisplayAlerts = False
    Range("A1").CurrentRegion.CreateNames True, False, False, False
    Application.DisplayAlerts = True
    Value = Range("Item_Price").Cells(3).Value

BTW I wouldn't use Value as a variable name, particularly when working with Excel (it is a property).

This post has been edited by andrewsw: 23 April 2013 - 08:39 AM

Was This Post Helpful? 2
  • +
  • -

#4 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 06:00 AM

Thank you so much for your early response, I will try the code and feedback.
Thanks again.
Was This Post Helpful? 0
  • +
  • -

#5 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 06:08 AM

When I use the "A1" for rang name it works fine, but when I use "Item_Price" I get the error "Application-defined or object-defined error"
:sad3:
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3817
  • View blog
  • Posts: 13,532
  • Joined: 12-December 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 07:34 AM

Click in a cell and press F5; it shows a dialog listing any defined names. How does Item Price appear in here? Perhaps it has another underscore at the end.

If nothing appears in the list, click into A1 and press Ctrl-A. Does this select the whole table? This is what CurrentRegion refers to in the code.

You also need to modify my code to use your sheet reference: XLSheet.Range("whatever").

This post has been edited by andrewsw: 24 April 2013 - 07:29 AM

Was This Post Helpful? 0
  • +
  • -

#7 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 08:39 AM

When I pressed F5, nothing appeared in the dialog but the table name.
I clicked cell A1 and Ctrl+A and it selected the whole table.
and regarding the modification of the code, sure sir I already did :bigsmile:
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3817
  • View blog
  • Posts: 13,532
  • Joined: 12-December 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 08:41 AM

Show your current code.

Also, use Ctrl-A then choose the Formulas tab, Create From Selection; tick only Top Row. The defined-names that are then created appear in a drop-down list to the top left - to the left of the formula bar. They should also then appear in the F5 dialog.

This post has been edited by andrewsw: 24 April 2013 - 08:43 AM

Was This Post Helpful? 1
  • +
  • -

#9 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 08:55 AM

Here is the real code, it is about importing data from excel sheet to an access database
Dim oXLApp As Object      
Dim oXLBook As Object
Dim oXLSheet As Object
Dim QualityData As Recordset
Set QualityData = MD.OpenRecordset(CmbSeason.Text & "_" & CmbYear.Text, 2) 
Set oXLApp = CreateObject("Excel.Application")  
Set oXLBook = oXLApp.Workbooks.Open(TxtImportFile.Text)  
Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
For I = 1 To oXLSheet.UsedRange.Rows.Count
QualityData.AddNew
    QualityData("Season") = oXLSheet.Range("Season").Cells(I).Value
    QualityData("ReportMonth") = oXLSheet.Range("ReportMonth").Cells(I).Value
    QualityData("VarietyName") = oXLSheet.Range("VarietyName").Cells(I).Value
QualityData.Update


Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3817
  • View blog
  • Posts: 13,532
  • Joined: 12-December 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 09:07 AM

So do the ranges named "Season", "ReportMonth" already exist in the worksheet? I've already described how you can check this.
Was This Post Helpful? 0
  • +
  • -

#11 dodyda  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 23-October 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 09:09 AM

Thank you sooooo much sir :winkiss:
It worked perfectly :bananaman:
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3817
  • View blog
  • Posts: 13,532
  • Joined: 12-December 12

Re: Get a cell value from excel range which is formatted as table in vb

Posted 24 April 2013 - 09:20 AM

View Postdodyda, on 24 April 2013 - 04:09 PM, said:

Thank you sooooo much sir :winkiss:/
It worked perfectly :bananaman:

Glad I could help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1