VB code to open a file that user chooses.

**gshock** · 03-11-2008, 01:01 PM

I'm trying to write a macro that will bring up the "File Open" dialog box, and allow the user to navigate to the directory where the file is located. It won't be the same file every time, and the files will likely have different names.

Thanks much,

-gshock

**VBA Noob** · 03-11-2008, 01:08 PM

Maybe

Please Login or Register  to view this content.

VBA Noob

**gshock** · 03-11-2008, 02:26 PM

VBA Noob,

It works, but not like I had hoped. Mostly, it's because I didn't ask the right questions. The user will open the Excel file containing the macro. I'm trying to write the macro so that it opens a different file (.out). My intention is to have the macro select the data in column A of the .out file, copy it, and paste it into cell A1 of the .xlt file.

I think my problem has to do with activating the right workbook/worksheet at the appropriate time. But that's just me guessing. Here's my code.

Please Login or Register  to view this content.

Thanks for your help.

-gshock

**Leith Ross** · 03-11-2008, 04:39 PM

Hello gshock,

There are 2 problems I see in the code. The first is the .out file extension. This is not a registered file type and won't be recognized by Excel as a workbook. Second, when you have 2 or more workbooks open, you need to set object variables that point to each workbook you are using. Ignoring the file extension problem, here is how your should be...

Please Login or Register  to view this content.

**gshock** · 03-12-2008, 08:03 AM

Hi Leith,
Thanks for your post. I appreciate anything you can teach me about this since I don't have any formal education in VBA. At my company, we are using Windows XP, so I just set the file associations for the .out file to be Excel. Even though it's not a "known" file type, when I click on the .out file, it opens in an Excel worksheet. I can then edit, copy, etc, and do a Save As to get the file to .xls, which is what we have been doing. Since I have the associations already set, I was hoping I could just open the file, copy the data to my active workbook, and run the macro for cleanup.

I'm going to input your suggestions into my code and play around with it some more. But if you can think of anything else, please let me know. Thanks again for your help.

EDIT: Leith, I just found a small glitch on my end. It seems that when the .out file is opened in Excel, it renames the worksheet to the same name of the file. Your code seems to work fine, but it doesn't find "sheet1" because it has a different name. Can I modify your code like this to solve this problem?

Modified code:

Please Login or Register to view this content.

Thanks,

-gshock

**Leith Ross** · 03-12-2008, 02:11 PM

Hello gshock,

I changed the macro so you can more easily change the Out Workbook sheet name. Change the code marked in blue below to the sheet name you are using.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**gshock** · 03-12-2008, 03:01 PM

Hi Leith,
Thanks again for your help. I copied the code as you wrote it and pasted it directly. I keep getting a 'Type mismatch' error on the line in red. I'm not sure what to trouble-shoot or why it's wrong. It's pretty much the same as in the last bit of code you gave me. I can't figure out why it's not working. The last piece worked fine.

[QUOTE=Leith Ross]

Please Login or Register  to view this content.

[QUOTE]

Thanks for your help.

-gshock

**Leith Ross** · 03-12-2008, 04:38 PM

Hello gshock,

Sorry about that, my fault for that problem. The variable FileToOpen should be declared as a Variant.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**gshock** · 03-12-2008, 04:43 PM

Hey Leith,
Don't worry about it. You've helped me so much already, I can hardly complain. I just didn't know what was wrong because I know so little about this kind of coding. C++ I understand. I just don't know the function calls so I can't begin to troubleshoot. Not yet, anyway.

Thank you so much for all of your help.

**gshock** · 03-13-2008, 02:10 PM

How can I modify this code so that I can make the OutSht a variant as well? As I open these files, the sheet is labeled as the same name of the file.

Thanks again for your help.

Originally Posted by Leith Ross

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Leith Ross** · 03-13-2008, 02:40 PM

Hello gshock,

Sometimes I miss the obvious. Try this version...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**gshock** · 03-13-2008, 03:23 PM

Leith,

Sorry to keep bugging you, but I'm still getting that 'Type Mismatch' error on this line:

Please Login or Register  to view this content.

I'm trying to troubleshoot the code myself, but I'm not having much luck. What am I missing?

Thanks for all the help so far. I can't thank you enough.

-gshock

**Leith Ross** · 03-13-2008, 11:11 PM

Hello gshock,

Try this code instead. Some systems set the return value to False if the user has clicked the Cancel button. Other systems simply return an empty string for either no selection or Cancel.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**gshock** · 03-14-2008, 07:42 AM

Hi Leith,
Thanks for the help. I seem to be making some progress. I select the file I want (as a test case) and get the following error:

'5.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

Not sure what this means, becuase the number '5' doesn't appear in any file name that I'm trying to use. I'm just using a sample to verify the code, and it's named "Sheet1.xls"

I appreciate any help you can give.

-gshock

**Leith Ross** · 03-14-2008, 04:24 PM

Hello gshock,

If you are getting this error after selecting a file from the Open File Dialog then something in your system is fouled up. Do experience any similar problems when using system dialogs on your system, or just with Excel? Today just seems to be the day of weird problems. Let me know.

Thanks,
Leith Ross

**gshock** · 03-17-2008, 07:59 AM

Hi Leith,
So far, I've only seen this with Excel. For now, I can comment out that section of code and just use the primary section of the macro. Having the user open the file is just a nice feature that I wanted to add. But the user can cut & paste the data also. And that works just as well.

Thanks for the help.

-gshock

**Leith Ross** · 03-17-2008, 10:26 AM

Hello gshockm

I should have asked this earlier, is the file opening prob;em with the .out or .xls extension? I'm betting it is the .out extension.

Thanks,
Leith Ross

**gshock** · 03-17-2008, 10:59 AM

Leith,

I didn't really notice at the time, but I think you're right. I was trying to open the .out files. I got the code to recognize the file type, but I didn't notice that I got the error only when I tried those files.

Thanks,

-gshock

**gshock** · 03-18-2008, 03:14 PM

I saved all of the .out files as .xls files. When I step through the code, I get the following error:

Run-time error '9':
Subscript out of range.

If I click on "Debug", the line in red is highlighted so I'm assuming the error is with something on that line

Here's what my code looks like up to this point.

Please Login or Register  to view this content.

Thanks in advance for any help you can provide.

-gshock

EDIT: Issue has been resolved. With enough playing around, I managed to figure it out. Thanks.

**vossy51** · 04-07-2013, 10:14 AM

How did you resolve the subscript out of range error?

**arlu1201** · 04-07-2013, 10:26 AM

vossy,

The subscript out of range error occurs when the sheet name in the code does not match the sheet name in the file or if the sheet name in the code does not exist in the file. Check the sheet names properly and then try again.