Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Empty cells in Excel...

Posted on 2010-01-12
7
Medium Priority
?
325 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

580 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