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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

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

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:

Please Login or Register  to view this content.

and elsewhere there are spaces in the filename.

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.

OK, I found an extra space in the folder name, which was causing the problem: I had OSAM Z Files (two spaces between OSAM and Z) for the folder name and OSAM Z Files (one space between OSAM and Z) in my code. Thank you for the tip, protonLeah.

So, now the file is opening but I am getting Run-time error '13': Type mismatch at the following line:

Please Login or Register  to view this content.

Here is the code I ran:

Please Login or Register  to view this content.

Can someone please tell me how to fix it.

Thanks,
Gos-C

Hi Gos-C

I noticed this in your code:

Please Login or Register  to view this content.

You could change that to:

Please Login or Register  to view this content.

Z4747 and Z4848 both being "Saskatchewan Providers"

Hi Norie,

Yes, that works. Thank you.

Hi SOS,

Thanks for pointing out the error. I corrected it to:

Please Login or Register  to view this content.

My code has another bug:

The AlignData gives Run-time '9': Subscript out of range at:

Please Login or Register  to view this content.

Here is the code (which is in a separate module):

Please Login or Register  to view this content.

Can you please tell me how to fix it?

Thank you,
Gos-C

zName is not assigned a value in this sub
Is it a public value or in the declarations area? or do you mean the sheet name is "zName"

Hi stevebliz,

Thanks for your quick response. I declared zName as public in the here:

Please Login or Register  to view this content.

zName would be both the workbook and the worksheet name.

Gos-C

The subscript out of range error probably means that the worksheet zName does not exist, or there is something wrong with the calculated values for n or Lastrow.

To troubleshoot, please put a breakpoint in your code at the line where it crashes, then add watches for c, zName, n and Lastrow to see if the values are what you expected/intended (including spelling and spaces). You might also Dim n as Long (your loop control var). You might try Dim'ing c as Variant vs. Range.

Hi Norie & protonLeah,

It's working! Thanks for you suggestions.

My codes were located as follows:

commandbutton1_click in - Sheet1,
DeleteColumns - Module1
SortColumns and Proper_Case - Module2
ColumnSort2, Headings2 and FormatSheet - Module3
AlignData - Module4

After reading you posts, I moved AlignData to Sheet1, and declared n as long . . . that did it. It worked!

One more question, please: How can I exit if I click the Cancel button for the input box here?

Please Login or Register  to view this content.

Thanks,
Gos-C

If zName="" Then....

Hi protonLeah,

The Input Box has an OK button and a Cancel button. Regardless of whether zName = "", when a user clicks the Cancel button, I want "Exit Sub" to occur.

Gos-C

When this form of the inputbox receives a "cancel" it returns "" to the receiving variable regardless of whether anything had been typed into the box. So the line:

Please Login or Register  to view this content.

should work for you, it does on my machine.

Hi protonLeah,

You are right, it works!

Thank you,
Gos-C

Hi everyone,

My code starts giving me an error:

A Microsoft Visual Basic window come up containing an X and 400. Does any know what that means?

Thanks,
Gos-C

From:
http://support.microsoft.com/kb/146864

I found:
400 Form already displayed; can't show modally (version 97)

Thanks for the info, protonLeah.

Gos-C

Hi all,

I am getting "Run-time error 1004: Application defined or object.defined error" with the following code:

Please Login or Register  to view this content.

Can you tell me why.

Thanks,
Gos-C

I think you will get that error if the code is in a sheet module and a different sheet is active. So, include the worksheet name, e.g., worksheets("sheet1").Range("H:H").Delete

protonLeah, you are the best! That was it; it works now.

Thanks for guiding me along. I am learning a lot.

Gos-C

Hi all,

I am down to the last part of my code, and I need your help again.

Here is my code:

Please Login or Register  to view this content.

I am getting Run-time error '52': Bad file name or number

How can I accomplish what I want?

Thank you,
Gos-C

As a test, I replaced the http:// with C:/ and the error went away. HTTP... is a URL so the DIR function won't work, but I don't have any experience with VB and URL connections to help you with, .

Thanks, protonLeah.

Can anyone else help me with this?

Thank you,
Gos-C