?
Solved

Empty cells in Excel...

Posted on 2010-01-12
7
Medium Priority
?
324 Views
Last Modified: 2012-05-08
Hello I have a Excel Sheet were I Looking for a row then take some cells
from excel..
Exemple I have all tags in a XML doc like this
<value sqltag="Power1" opctag="TEST.Power1" />
<value sqltag="Power2" opctag="TEST.Power2" />  and so on...



Public Class XMLData
    ''' <summary>
    ''' Get plc write tags from xml file
    ''' </summary>
    ''' <returns>Dictionary of xml tags</returns>
    Public Shared Function PLCWriteTags() As Dictionary(Of String, String)
        Dim writeopcdata = New Dictionary(Of String, String)

        ' Read XML documnet
        Dim doc = XDocument.Load("TagData.xml")

        ' Read xml tags from document
        For Each tag In doc.Elements().First().Element("writeopcvalues").Elements()
            writeopcdata.Add(tag.Attribute("sqltag").Value, tag.Attribute("opctag").Value)
        Next

        ' Return xml tags
        Return writeopcdata
    End Function




This works al the time when the cells I looking in have som value or something written in it.. But when it´s totally empty It just stops on this cell and will not go on.. I want to if the cells is empty just looking to next and next and so on...
I have no idees what I can do.. I looking forward to get some tips. I can give more info If you don´t understand what I mean!
Thank you all...
0
Comment
Question by:kavvis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 

Author Comment

by:kavvis
ID: 26291397
sorry I didn´t copy everything.. here it is!

    Public Sub WriteExcelValuesToPLCTest1(ByVal excelData As DataRow)
        Dim xmldata = New XMLData()

        ' Loop through all xml tags
        For Each xmltag In xmldata.PLCWriteTags()
            ' Lopp through all OPC items
            For Each item As OPCAutomation.OPCItem In Me.plcWriteTest1Group.OPCItems
                ' If xml tag found in OPC group
                If item.ItemID = xmltag.Value Then
                    ' Write value to PLC
                    item.Write(excelData.Item(xmltag.Key).ToString().Replace(".", ","))
                End If
            Next
        Next
0
 

Author Comment

by:kavvis
ID: 26292450
I think I want something like this..

  If item.ItemID = xmltag.Value Then
                   
                    item.Write(excelData.Item(xmltag.Key).ToString().Replace("""", "0"))

But I can´t get it to work...
0
 
LVL 6

Expert Comment

by:CowboyJeeper
ID: 26293179
Is this all the code?  What line does it freeze on?  Doing something like this should work to skip empty cells.


if not cells().value = empty then
    'do something
end if

Open in new window

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:kavvis
ID: 26293697
   Public Sub WriteExcelValuesToPLCTest1(ByVal excelData As DataRow)
        Dim xmldata = New XMLData()

        ' Loop through all xml tags
        For Each xmltag In xmldata.PLCWriteTags()
            ' Lopp through all OPC items
(jump here(1))          
 For Each item As OPCAutomation.OPCItem In Me.plcWriteTest1Group.OPCItems
                ' If xml tag found in OPC group
                If item.ItemID = xmltag.Value Then
                    ' Write value to PLC
                    item.Write(excelData.Item(xmltag.Key).ToString().Replace(".", ","))
                       If Not String.IsNullOrEmpty(item.Value) Then  
(Jump to 1)
                       End If
               End If
            Next
        Next


That is what I want to do... or the best thing I wolud like to do is if it´s empty then Write "0"   that should be the best thing.. but I can´t get it to work...
0
 
LVL 6

Accepted Solution

by:
CowboyJeeper earned 2000 total points
ID: 26293831
Would something like this work for you?
Public Sub WriteExcelValuesToPLCTest1(ByVal excelData As DataRow)
        Dim xmldata = New XMLData()

        ' Loop through all xml tags
        For Each xmltag In xmldata.PLCWriteTags()
            ' Lopp through all OPC items
(jump here(1))
 For Each item As OPCAutomation.OPCItem In Me.plcWriteTest1Group.OPCItems
                ' If xml tag found in OPC group
                If Item.itemID(xmltag.Key).toString = vbNullString Then
                    ' do what you need
                ElseIf Item.itemID = xmltag.Value Then
                    ' Write value to PLC
                    Item.Write (excelData.Item(xmltag.Key).toString().Replace(".", ","))
               End If
            Next
        Next

Open in new window

0
 

Author Comment

by:kavvis
ID: 26294012
OK thank you.. I think I tested that way... but right now I think I know that this it not the problem...
The problem that´s happend is very hard do explain I think...
After I have  get a tag thats contain nothing then something strange happends... I don´t get any new Item.value...  orevery next item.value´s gets "nothing" it´s like almost like my SQL query get´s mess up when it comes to a empty cell....

So somethings else is wrong but can´t explain it...  I get nothing;)

thank you for you took your time I hope I figure this out. Al inputs is very glad to get..
0
 

Author Comment

by:kavvis
ID: 26294115
       For Each xmltag In xmldata.PLCWriteTags()
            ' Lopp through all OPC items
 (  JUMP HERE 1)         For Each item As OPCAutomation.OPCItem In Me.plcWriteTest1Group.OPCItems
                ' If xml tag found in OPC group
                If item.ItemID = xmltag.Value Then

                    ' Write value to PLC
                    item.Write(excelData.Item(xmltag.Key).ToString().Replace(".", ","))
                    If Not String.IsNullOrEmpty(item.Value) Then
(If that was true then JUMP TO  1  (How do I do this???)
                    End If
                End If
            Next
        Next


I think this is the solution but I don´t know how to write it...  
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question