parsing data into excel

[Vladdic]

I've used VB slightly, but never gotten familiar with VBA. Is it possible to create a form that a user can input a string of data into, then parse that the integers out of that string into the next blank cell?

Example: ID: #15149, Budget: 2 cr, agriculture: 0, mining: 0, processing: 0, production: 0, physics: 0, chemics: 0, medical: 0, weapons: 1, drives: 0, construction: 0

Parsing this would return 15149, 2, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0 into row n(next blank row), columns a-l.

Or is there some way to set agriculture, mining, etc as constants and return the integer following them to the next blank cell?

[RobDog888]

Welcome to the Forums.

Yes this can be done. Their may be a better way though. What about having
a separate textbox for each cell entry?

[Vladdic]

Sadly, that would defeat the whole purpose of the macro, which is to make it so a large number of variants of the above posted string can be quickly sorted. I started trying to do this in a stand-alone VB application using regular expressions, and was able to sort the string, but had no idea how to import the data into excel.

[RobDog888]

This short Excel Tutorial should help.

Wouldnt using a userform also defeat the purpose too. Entering data directly into Excel be faster or are you
reading data in from a textfile or ???

[Vladdic]

I think the tutorial might help me hack something together.

The string I'm pasting in will be coming from my browser window, and there will be a large number (100-1000s) of variations of it. I'm trying to create a way I just copy, paste, then click and have it sort out the string without me having to bother with it.

Sorry if this isn't so clear. I have the sneaking suspicion that I'm just going to have to sit down and learn VB(A) in order to do this, instead of just hacking it all together =)

[RobDog888]

Well that may be true, but we are here to help you with any questions when they come up. The thing to
do is to break it down into smaller manageable sections. First you need to finish you userform so it populates
Excel correctly. Then you need to handle all the variations of the data.

VB and VBA are very similar. You can even automate Excel from VB, read the data from the web page and directly
enter it into Excel without copying and pasting. It just depends on how much work you want to do.

[Vladdic]

Appreciate the help, I'll probably get back to once I'm done with the next 1k pages of my Mastering VB .NET book

[RobDog888]

You dont have to read all 1000 before you start but it does help. What I do is learn as the need arises.
Like with this one you will want to learn how to inetract with the userform and the excel grid. Then, learn some
of the parsing and validation techniques and that should get your main issue solved.

Search the Forums for everything will be here that you need, if not let me know and I will try to help.

[Vladdic]

Alright.. one more go at this. Maybe this is in the wrong forum, but you seem to know your stuff.. so here goes

mports System.IO
Imports System.Net
Imports System.Text.RegularExpressions
 
Public Class Form1
    Sub AppendResults(ByVal msg As String)
        txtRes.AppendText(msg & ControlChars.CrLf)
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e _      As System.EventArgs) Handles Button1.Click
        Dim r As Regex
        Dim m As Match
        r = New Regex("\d+")
        Dim mc As MatchCollection = r.Matches(txtFor.Text)
        For Each m In mc
            AppendResults(m.Value)
 
        Next
 
 
 
    End Sub

This is returning all of the integer values in this user (txtFor textbox) inputted string:

ID: #15207, Budget: 2 cr, agriculture: 0, mining: 0, processing: 0, production: 0, physics: 0, chemics: 1, medical: 0, weapons: 0, drives: 3, construction: 0

to my txtRes textbox in the following format:
15207
2
0
0
0
0
0
1
0
0
3
0

How would I populate the next blank row with these values? Am I even on the right track here? Thanks for your patience and your help.

[RobDog888]

This should get you started.

Imports System.IO
Imports System.Net
Imports System.Text.RegularExpressions
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
[color=dimgray]Region " Windows Form Designer generated code "[/color]
 
    Sub AppendResults(ByVal msg As String)
        txtRes.AppendText(msg & ControlChars.CrLf)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim r As Regex
        Dim m As Match
        r = New Regex("\d+")
        Dim mc As MatchCollection = r.Matches(txtFor.Text)
        For Each m In mc
            AppendResults(m.Value)
        Next
 
        Dim oApp As Excel.Application
        Dim oWB As Excel.Workbook
        Dim rLastRow As Excel.Range
        Const xlCellTypeLastCell = 11
        Dim i As Integer
        Dim iRow As Integer
        Dim iCol As Integer
        Dim bOpen As Boolean
 
        Try
            oApp = GetObject(, "Excel.Application")
            If oApp Is Nothing Then oApp = New Excel.Application
            For Each oWB In oApp.Workbooks
                If oWB.Name = "MyExcelDemo" Then
                    bOpen = True
                    Exit For
                Else
                    bOpen = False
                End If
            Next
            If bOpen = True Then
                'Already open
                oWB = oApp.Workbooks("MyExcelDemo")
            Else
                'Open your wb
                oWB = oApp.Workbooks.Open("C:\MyExcelDemo.xls")
            End If
            rLastRow = oWB.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)
            iRow = rLastRow.Row
            For i = 1 To 12
                oWB.Sheets(1).cells(i, iRow) = mc(i - 1).Value
            Next
        Catch ex As Exception
            Throw New System.Exception("Excel Error!")
        End Try
 
    End Sub
 
End Class

[Vladdic]

Thanks! Where is iCol used at.. and Const xlCellTypeLastCell = 11 isn't declared?

Silly me.. got the constant declared.. but iCol isn't used anywhere?

[Vladdic]

Alright.. got iCol taken care of.. now.. how to make it continue to write to my spreadsheet without trying to open a new one everytime?

[RobDog888]

I put iCol in there because I though that maybe we would need dynamic used column range too?
The GetObject should be attaching to any existing excel instance.

This line is where you need to place a breakpoint and test if its working correctly.

If oApp Is Nothing Then oApp = New Excel.Application

I didnt test the code so there may be some issues still.

[RobDog888]

Ok, I didnt place the oApp object in the correct scope.

Imports System.IO
Imports System.Net
Imports System.Text.RegularExpressions
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
    [b]Private oApp As Excel.Application[/b]
 
[color=DimGray]Region " Windows Form Designer generated code "[/color]
 
    Sub AppendResults(ByVal msg As String)
        txtRes.AppendText(msg & ControlChars.CrLf)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim r As Regex
        Dim m As Match
        r = New Regex("\d+")
        Dim mc As MatchCollection = r.Matches(txtFor.Text)
        For Each m In mc
            AppendResults(m.Value)
        Next
 
        Dim oWB As Excel.Workbook
        Dim rLastRow As Excel.Range
        Const xlCellTypeLastCell = 11
        Dim i As Integer
        Dim iRow As Integer
        'Dim iCol As Integer
        Dim bOpen As Boolean
 
        Try
            oApp = GetObject(, "Excel.Application")
            If oApp Is Nothing Then oApp = New Excel.Application
            For Each oWB In oApp.Workbooks
                If oWB.Name = "MyExcelDemo" Then
                    bOpen = True
                    Exit For
                Else
                    bOpen = False
                End If
            Next
            If bOpen = True Then
                'Already open
                oWB = oApp.Workbooks("MyExcelDemo")
            Else
                'Open your wb
                oWB = oApp.Workbooks.Open("C:\MyExcelDemo.xls")
            End If
            rLastRow = oWB.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)
            iRow = rLastRow.Row
            For i = 1 To 12
                oWB.Sheets(1).cells(i, iRow) = mc(i - 1).Value
            Next
        Catch ex As Exception
            Throw New System.Exception("Excel Error!")
        End Try
 
    End Sub
 
End Class

[Vladdic]

I really appreciate all the help you've given me and your inhuman patience with my ignorance. Everything seems to be working accept I keep getting an unhandled exception for this peice of code:

Catch ex As Exception
            Throw New System.Exception("Excel Error!")
        End Try

Which seems to be referring back to:

oApp = GetObject(", Excel.Application")

When I ignore this error, and compile it anyways, the program works for the first string I enter, but for every additional string it tries to open/reopen my spreadsheet. Would it be easier to write all the data from txtFor directly to excel instead of it passing through txtRes? I originally did this to test whether or not the code I wrote was sorting the string properly, and it is really no longer necessary.

Again, thank you for all your help. I know you are a busy person, and if this problem has taken too much of your time, and you don't feel like fiddling with it anymore, I'll understand completely.

[RobDog888]

Looks like you may have had an issue pasting the code in. It should be...

oApp = GetObject(, "Excel.Application")

Your double quote was to the left of the comma. It needs to be just around the Excel.Application only.

[Vladdic]

Fixed that, but still getting the unhandled exception at:

Throw New System.Exception("Excel Error!")

[RobDog888]

I think you also need the System namespace.

Imports System

[Vladdic]

Still no go. Appreciate all your help, but I think I'm going to put this project on hold until I get a better idea of what I'm doing.. my guess/check/beg for help method is starting to get frustrating hehe

[RobDog888]

Noooo, dont give up yet. So close. Change it to this.

Catch ex As Exception
    MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

[Vladdic]

Arg.. now I get cannot create active x component..?
Err.. if I have excel open.. it will put the string where I want it, but when I do another string it tries to reopen it? How about we'll just assume that I always have excel and the workbook open before using this program so we don't have to worry about that?

[RobDog888]

Ok, I have created a test and got the GetObject working so it doesnt open another instance.
I will post it in a minute, but I want to get the regex working.

I pasted in: "ID: #15149, Budget: 2 cr, agriculture: 0, mining: 0, processing: 0, production: 0, physics: 0, chemics: 0, medical: 0, weapons: 1, drives: 0, construction: 0"
and its not parsing correctly. I havent used regex too much so if you could assist me on that I can test if
everything is working correctly.

[RobDog888]

Nevermind, I got the Regex working and the entire code.
"ID: #15149, Budget: 2 cr, agriculture: 0, mining: 0, processing: 0, production: 0, physics: 0, chemics: 0, medical: 0, weapons: 1, drives: 0, construction: 0"

Imports System.IO
Imports System.Net
Imports System.Text.RegularExpressions
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
    Private oApp As Excel.Application
 
#Region " Windows Form Designer generated code "
 
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Windows Form Designer.
        InitializeComponent()
 
        'Add any initialization after the InitializeComponent() call
 
    End Sub
 
    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents txtRes As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents txtFor As System.Windows.Forms.TextBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.txtRes = New System.Windows.Forms.TextBox
        Me.txtFor = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'txtRes
        '
        Me.txtRes.Location = New System.Drawing.Point(136, 136)
        Me.txtRes.Name = "txtRes"
        Me.txtRes.TabIndex = 0
        Me.txtRes.Text = ""
        '
        'txtFor
        '
        Me.txtFor.Location = New System.Drawing.Point(72, 56)
        Me.txtFor.Name = "txtFor"
        Me.txtFor.TabIndex = 1
        Me.txtFor.Text = ""
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(176, 216)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "Button1"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.txtFor)
        Me.Controls.Add(Me.txtRes)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)
 
    End Sub
 
#End Region
 
    Sub AppendResults(ByVal msg As String)
        txtRes.AppendText(msg & ControlChars.CrLf)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim r As Regex
        Dim m As Match
        r = New Regex("\d+")
        Dim mc As MatchCollection = r.Matches(txtFor.Text)
        For Each m In mc
            AppendResults(m.Value)
        Next
 
        Dim oWB As Excel.Workbook
        Dim rLastRow As Excel.Range
        Dim rLastCol As Excel.Range
        Const xlCellTypeLastCell = 11
        Dim i As Integer
        Dim iRow As Integer
        Dim iCol As Integer
        Dim bOpen As Boolean
 
        Try
            oApp = GetObject(, "Excel.Application")
            If oApp Is Nothing Then oApp = New Excel.Application
            For Each oWB In oApp.Workbooks
                If oWB.Name = "MyExcelDemo.xls" Then
                    bOpen = True
                    Exit For
                Else
                    bOpen = False
                End If
            Next
            If bOpen = True Then
                'Already open
                oWB = oApp.Workbooks("MyExcelDemo.xls")
            Else
                'Open your wb
                oWB = oApp.Workbooks.Open("C:\MyExcelDemo.xls")
            End If
            oApp.Visible = True
            rLastRow = oWB.Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell)
            iRow = rLastRow.Row + 1
            rLastCol = oWB.Sheets(1).usedrange.specialcells(xlCellTypeLastCell)
            iCol = rLastCol.Column
            For i = 1 To mc.Count
                oWB.Sheets(1).Cells(iRow, i).Value = mc(i - 1).Value
            Next
            oWB.Save()
        Catch exp As Exception
            MessageBox.Show(exp.Message, exp.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
 
    End Sub
 
End Class

[RobDog888]

You can make your program compatible with Excel 2002 as described in this quote from MSDN:

Using the Office XP PIAs and the Office 2003 PIAs
PIAs are version specific, so you should use the same version of the PIAs as the application version. For example, if you
want to migrate your Microsoft Office XP managed code solution to Microsoft Office 2003, you should recompile the
Office XP managed code solution with Office 2003 PIAs.

You should also have two different setup packages—one for Office XP and another for Office 2003—if you intend for
the same solution to work in both Office XP and Office 2003. Deployment of PIAs for Office XP is very different from that
for Office 2003. For further information about these differences, see the ReadMe file included in the Office XP PIAs
download. As emphasized in Avoiding Redistribution of the Office 2003 PIAs, no solution deployment scenario should try
to redeploy the Office 2003 PIAs. If your solution relies on the Office 2003 PIAs, it should use Office 2003 to deliver the PIAs.

[Vladdic]

Wow.. I just opened up a whole new can of worms. I think my friends can just go out and get Office 2003.

[Vladdic]

Hrm.. for some reason it won't let me access the microsoft excel 10 object library, only the 11. Any suggestions?

[RobDog888]

What do you have installed on that system? Both? Did you install the PIA? If you installed the PIAs then it will only be
one version allowed at a time.

[Vladdic]

Yeah.. my friends can just go blow the extra money on Office 2003

Thanks again though.

[RobDog888]

Have you tried Late Binding ?

Dim oApp as Object
oApp = CreateObject("Excel.Application")
'Blah, blah, blah...

Dont use any references to Office or Excel.