# VBScript Zipcode Lookup



## Powlaz (Aug 27, 2005)

I have a unique Excel spreadsheet that I would like to make into a standalone program. The spreadsheet consists of two columns: zip codes and the profit center we associate with that zip code. What makes it unique is that it runs as a program (.exe) with no visible sign of the spreadsheet or Excel at all. Unfortunately to do this a customized version of Excel (http://orlando.mvps.orgXLtoEXEMore.asp) is packaged inside the .exe and powers the "program". This version of Excel conflicts with the version we are running on our workstations so I need to make this little lookup program a standalone program.

I think that VBScript will allow me to do what is needed but I really don't know where to start. Is there anyone out there that can help me with a script that prompts the user to enter a zip code and then return the data associated with it? I put my code from the spreadsheet below. Below is the VBA code from my workbook. It basically searches the 'zip codes' column and returns the data in the associated 'Profit Center' column. There are one or two lines that prevent the user from entering in bad data and the "program" stays open to be used again and again as needed.

Here is my VBA code:


```
Option Explicit

Private Sub Image1_Click()

Call Do_Search

End Sub

Private Sub CommandButton2_Click()

UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox1.SetFocus

End Sub

Sub Do_Search()

Dim myRange As Range

If TextBox1.Value <> "" Then

Set myRange = Sheets("Profit Center Lookup").Range("A:A").Find(TextBox1.Value, LookIn:=xlValues)

    If Not myRange Is Nothing Then

        TextBox2.Value = myRange.Offset(0, 1)
    
    Else

        TextBox2.Value = "No Match"

    End If
    
End If

End Sub

Private Sub Label1_Click()

End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then

    Do_Search
    
End If


End Sub

Private Sub TextBox1_Change()

OnlyNumbers

If Len(TextBox1.Value) > 5 Then

     MsgBox "Please Enter a 5 Digit Zip Code Only!", vbExclamation, "Invalid Zip Code"
     
     UserForm1.TextBox1.Value = ""
  
End If
    

End Sub
Private Sub OnlyNumbers()
 If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.Value) And .Value <> vbNullString Then

                 MsgBox "Sorry, there are no letters in US zip codes", vbExclamation, "Invalid Zip Code"

                .Value = vbNullString

            End If

        End With

    End If

End Sub


Private Sub UserForm_Initialize()

UserForm1.TextBox1.SetFocus

End Sub

Private Sub UserForm_Terminate()

MsgBox "App would normally close now!"
Application.Visible = True

'ThisWorkbook.Close False
'Application.Quit

End Sub
```
Thanks for any help you can give me. I spoiled everyone with this little spreadsheet program of mine but now it's making them mad and I'm supposed to deliver a less problematic replacement and have no idea where to start. I'm not married to the idea of using VB either by the way. I don't really want to do it in a batch file but other than that I have no real preference.

Thanks,

MJ


----------



## Jimmy the Hand (Jul 28, 2006)

I'm a bit unsure what you actully need. Let me put it my way, then you say whether I'm right or wrong.

You want a small VB application, a small form, actually, that can look up data in a closed (or invisible open) Excel workbook. The user enters a zip code into the form, the program checks if it's a valid code, then searches the workbook for the zip code, and returns the profit center associated with the zip code. Afterwards, the form stays open, ready to receive more zip code inputs.

Is that right?

Jimmy


----------



## Powlaz (Aug 27, 2005)

Jimmy The Hand - Thank you for the reply. I'm so happy to hear from someone. I am also happy that you rephrased what I wrote as it gives me some insight into how my post is being read.

What you described is exactly what I already have. I would like to remove Excel from the equation so what I'm really looking for is a standalone application to replace what I have. I thought VB or a VBScript could do it but I'm not really partial to the language that the program is composed from, moreso that I can't continue to use the Excel based lookup that I have.

Is it doable?

MJ


----------



## Jimmy the Hand (Jul 28, 2006)

Actually, VB (Visual Basic) is not that much different from VBA (Visual Basic for Applications). (The latter is the coding language of Microsoft Office applications.) So, if you know VBA, which your code proves you do, then VB shouldn't be too difficult to use.

About the problem at hand, I think we still need to do some clarification. Let me tell you why.

What I have in mind is a small VB application, which is only a Form and a few other controls, like textboxes or buttons, etc. The program itself is not a distorted version of Excel, nor does it contain any portions of Excel. However, it can _call on the Excel application which is already installed on the current computer_.

Technically, the program creates an "Excel.Application" type object, and assigns a value to this object by starting one instance of the installed Excel application. Then, the VB program will have a complete control over that particular Excel instance, and can do whatever you can do in Excel VBA. For example, you can open any workbook, search through them, modify them, save them, etc. As a bonus, you can set the Excel application invisible, so the user won't see anything of what's going on in the background.

So, the VB standalone application is absolutely independent of Excel, it only _uses _the preinstalled Excel for its own purposes. I think this negates any compatibility issues, except when the workbook itself is not compatible with the installed Excel.

However, if you want to stay away from anything remotely related to Excel, then I can't help you, because you data is in a workbook, and I'm not familiar with the data structure of Excel workbooks.

What's your opinion?

Jimmy

EDIT:
Of course, if the zip - profit center data is not necessarily in an Excel workbook, but in a text file, for example, then the job can be done purely with VB. Is that what you want?


----------



## Powlaz (Aug 27, 2005)

Jimmy The Hand - I'm very sorry for not replying sooner. I thought for sure I replied last week but . . . it's not here.

I feel like I should comment completely on your last reply given that you took the time to try to further understand my need, explain yourself, present a solution that will work BUT I rather like the idea of putting my data in a plain text file and having everything done right in VB. Will a CSV file for the data do the job? How do I get the VB app rolling?


----------



## Jimmy the Hand (Jul 28, 2006)

Took some time to get around to it, but here you go.
The following code is a full specification of a windows application written in Visual Basic.

```
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class frmMain
   Inherits System.Windows.Forms.Form

   'Form overrides dispose to clean up the component list.
   <System.Diagnostics.DebuggerNonUserCode()> _
   Protected Overrides Sub Dispose(ByVal disposing As Boolean)
      If disposing AndAlso components IsNot Nothing Then
         components.Dispose()
      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.
   <System.Diagnostics.DebuggerStepThrough()> _
   Private Sub InitializeComponent()
      Me.TextBox1 = New System.Windows.Forms.TextBox
      Me.Label1 = New System.Windows.Forms.Label
      Me.Label2 = New System.Windows.Forms.Label
      Me.TextBox2 = New System.Windows.Forms.TextBox
      Me.SuspendLayout()
      '
      'TextBox1
      '
      Me.TextBox1.Location = New System.Drawing.Point(178, 19)
      Me.TextBox1.Name = "TextBox1"
      Me.TextBox1.Size = New System.Drawing.Size(102, 20)
      Me.TextBox1.TabIndex = 0
      '
      'Label1
      '
      Me.Label1.AutoSize = True
      Me.Label1.Location = New System.Drawing.Point(12, 19)
      Me.Label1.Name = "Label1"
      Me.Label1.Size = New System.Drawing.Size(105, 13)
      Me.Label1.TabIndex = 1
      Me.Label1.Text = "Input ZIP code here:"
      '
      'Label2
      '
      Me.Label2.AutoSize = True
      Me.Label2.Location = New System.Drawing.Point(12, 49)
      Me.Label2.Name = "Label2"
      Me.Label2.Size = New System.Drawing.Size(120, 13)
      Me.Label2.TabIndex = 3
      Me.Label2.Text = "Associated Profit Center"
      '
      'TextBox2
      '
      Me.TextBox2.Location = New System.Drawing.Point(178, 46)
      Me.TextBox2.Name = "TextBox2"
      Me.TextBox2.Size = New System.Drawing.Size(234, 20)
      Me.TextBox2.TabIndex = 2
      '
      'frmMain
      '
      Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
      Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
      Me.ClientSize = New System.Drawing.Size(424, 89)
      Me.Controls.Add(Me.Label2)
      Me.Controls.Add(Me.TextBox2)
      Me.Controls.Add(Me.Label1)
      Me.Controls.Add(Me.TextBox1)
      Me.Name = "frmMain"
      Me.Text = "ZIP Code LookUp"
      Me.ResumeLayout(False)
      Me.PerformLayout()

   End Sub
   Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
   Friend WithEvents Label1 As System.Windows.Forms.Label
   Friend WithEvents Label2 As System.Windows.Forms.Label
   Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
   Dim ZIPS As Collection

   Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

   End Sub

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim FN As String, TxtLine As String, ZipCode As String, ProfitCenterName As String
      Dim objReader As System.IO.StreamReader

      FN = "ZIP_Code_Database.csv"
      objReader = New System.IO.StreamReader(FN)
      ZIPS = New Collection
      While Not objReader.EndOfStream
         TxtLine = objReader.ReadLine
         ZipCode = Mid(TxtLine, 1, 5)
         ProfitCenterName = Mid(TxtLine, 7)
         ZIPS.Add(ProfitCenterName, ZipCode)
      End While
   End Sub

   Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
      Dim PC_Code As String
      Try
         PC_Code = ZIPS(TextBox1.Text)
      Catch ex As Exception
         PC_Code = "invalid or unknown ZIP code"
      End Try
      TextBox2.Text = PC_Code
   End Sub
End Class
```
The program is a simple form, which has 2 fields. One for ZIP code input, the other for feedback.
When the user types in the zip code, the program, on the fly, checks the database and gives feedback. THe feedback appears in the 2nd field of the form, and is either a Profit Center name, or the string: "invalid or unknown ZIP code".
The program needs a database with the exact name of "ZIP_Code_Database.csv". The database file must be located in the same directory as the exe file. As for the structure of the database, I've attached a sample file for you to look on.

You'll probably need to install Microsoft Visual Studios Express edition (freely downloadable form Microsoft web site) to compile the code and build an exe file. In case you don't have MS Visual Studios, and don't want to download & install it, I can give you the exe file itself. However, as far as I know, it is against forum rules to post exe files here, so I can only send it in email. Please give me your email address in a private message, if you need the exe file.


----------

