Select Case Statement

Hi all,

I have tried my best but can't get the following code to do what I want it to do:

Option Explicit
Dim zName As String, prov As string

Private Sub commandbutton1_click()
Dim response As Integer
zName = InputBox("Please enter the Z-file name", "Enter Z-File Name", "Z-file name")
response = MsgBox("Is " & UCase(zName) & " the correct file name?", vbYesNo + vbQuestion, "Z-File Name")
If response = vbYes Then
Call SelectProvinceCase
Application.Workbooks.Open ("http://sharepoint/sites/hbm/Audit/OSAM Z FILES/" & prov & "/" & zName & ".csv")
If Left(zName, 5) = "Z4343" Or "Z4545" Then
Call AlignData
End If
Call DeleteColumns
Call SortColumns
Call ColumnSort2
Call Headings2
Call FormatSheet
End Sub

Sub SelectProvinceCase()
prov = Left(zName, 5)
Select Case prov
    Case Z4141
        prov = "Nova Scotia Providers"
    Case Z4242
        prov = "Quebec Providers"
    Case Z4343
        prov = "NFLD Providers"
    Case Z4444
        prov = "NEW Brunswick Providers"
    Case Z4545
        prov = "PEI Providers"
    Case Z4646
        prov = "Ontario Providers"
    Case Z4747
        prov = "Saskatchewan Providers"
    Case Z4848
        prov = "Saskatchewan Providers"
    Case Z4949
        prov = "Alberta Providers"
    Case Z5050
        prov = "British Columbia Providers"
    Case Else
        MsgBox "Please verify the z-file name - the provider number appears to be incorrect."
    Exit Sub
End Select
End Sub

The SelectProvinceCase is not working so I know it is not written correctly.

If the first five characters of the file name is Z4949, for example, I want Alberta Providers to be inserted where prov is in

Application.Workbooks.Open ("http://sharepoint/sites/hbm/Audit/OSAM Z FILES/" & prov & "/" & zName & ".csv")

so that the code will open the file.

Can someone fix the code for me, please.

Thank you,
Gos-C

Hi Norie,

I had quotes around them but that didn't work.

Gos-C

Hi Norie,

Sorry, I edited my first post to show prov as String. I was changing different things just to see if I can get it to work.

When I run the code I get Run-time error '1004'. Microsoft Office Excel cannot access the file . . . and I noticed that the file path does not include the "prov" folder, which it should have received from the Select Case Statement.

Edited . . . I just realized I need to call SelectProvinceCase - the path is now correct, but it still is not working!

Gos-C

You seem to be missing an End If in the commandbutton1_click() sub.