Select Case Statement

**Norie** · 06-05-2008, 01:22 PM

Why aren't you using quotes for Z4141 etc in the case statements?

**Gos-C** · 06-05-2008, 01:42 PM

Hi Norie,

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

Gos-C

**Norie** · 06-05-2008, 01:49 PM

Does that code even compile?

For a start there is no such data type as variable which you seem to have here.

prov As Variable

Then since you don't have quotes around Z4141 etc then VBA would probably be regarding them as variables.

And since you have Option Explicit set then VBA should really be flagging that up.

When you say it doesn't work, how exactly?

**Gos-C** · 06-05-2008, 02:07 PM

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

**Leith Ross** · 06-05-2008, 10:13 PM

Hello Gos-C,

Here is how the syntax should be for comparing strings in a Select Case statement.

Sub SelectProvinceCase()

  prov = Left(zName, 5)
    Select Case prov
      Case Is = "Z4141"
        prov = "Nova Scotia Providers"
      Case Is = "Z4242"
        prov = "Quebec Providers"
      Case Is = "Z4343"
        prov = "NFLD Providers"
      Case Is = "Z4444"
        prov = "NEW Brunswick Providers"
      Case Is = "Z4545"
        prov = "PEI Providers"
      Case Is = "Z4646"
        prov = "Ontario Providers"
      Case Is = "Z4747"
        prov = "Saskatchewan Providers"
      Case Is = "Z4848"
        prov = "Saskatchewan Providers"
      Case Is = "Z4949"
        prov = "Alberta Providers"
      Case Is = "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

Sincerely,
Leith Ross

**protonLeah** · 06-05-2008, 10:17 PM

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

**Gos-C** · 06-05-2008, 10:53 PM

Hi Leith, I have that.

Hi protonLeah, I also have that.

(Sorry, the inadvertently omitted them in my post - I had remove them to try to figure out the problem.)

However, I am still getting the error that MS Excel cannot access the file. The file is saved as a ".csv". I know that the path is correct and it is not being used.

Any idea for the error?

Thank you,
Gos-C

**protonLeah** · 06-06-2008, 09:45 PM

I seem to recall that spaces are not allowed in http addresses, they use either the underscore or the %20 to separate words. Are you sure your file names are correct? For instance, can you try:

OSAM%20Z%20FILES, 
or
OSAM_Z_FILES

and elsewhere there are spaces in the filename.

**kumar_viswa2003** · 06-07-2008, 01:15 AM

Avoid using spaces or underscores in such codings.

I had similar trouble in which I used Underscore. But it is not HTTP link. After removing the underscore from the file name, it worked fine.