Macro to select files and then run different macro on selected file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

beginner1.mat@hotmail.com - 31 Jan 2008 00:02 GMT

I am familiar with using the record macro function in excel, but I
would like to have excel prompt me as to what file I would like to
select to run a different macro (a macro that strips data and makes
graphs). Ideally, I would like excel to prompt me to select a folder
(which has all raw excel files) and then run the macro (to make
graphs) on all the .xls files in the folder. Is this possible?

Reply to this Message

Conan Kelly - 31 Jan 2008 00:20 GMT

beginner1,

You could try adding this to your "strips data and makes graphs" macro near
the top so this stuff happens before any of the other lines of code get
executed:

dim pstrFilePath as string
pstrFilePath = Application.GetOpenFilename
Application.Workbooks.Open pstFilePath

Now everytime that macro is run, it will prompt you for a file to open, open
that file, and continue on with the code.

If you want to be able to run this macro without being prompted, then put
those 3 lines of code in a separate sub procedure/routine and then call your
other macro as the last line of code in this separate sub.

HTH,

Conan

>I am familiar with using the record macro function in excel, but I
> would like to have excel prompt me as to what file I would like to
> select to run a different macro (a macro that strips data and makes
> graphs). Ideally, I would like excel to prompt me to select a folder
> (which has all raw excel files) and then run the macro (to make
> graphs) on all the .xls files in the folder. Is this possible?

Reply to this Message

beginner1.mat@hotmail.com - 31 Jan 2008 01:55 GMT

On Jan 30, 4:20 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:

> beginner1,
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Thanks for the code. I tried inserting it to the beginning of my
other macro, but got an error on this line:

Application.Workbooks.Open pstFilePath

run-time error '1004':
"could not be found. Check the spelling of the file name, and verify
that the file location is correct.
If you are trying to open the file from your list of most recently
used files, make sure that the file has not been renamed, moved, or
deleted.

Am I missing something?

Reply to this Message

Conan Kelly - 31 Jan 2008 02:22 GMT

beginner1,

If you copied and pasted the code I entered, then that is the problem. I
see a typo now. I didn't test this code before I sent it to you.

the line that reads:

Application.Workbooks.Open pstFilePath

...change to:

Application.Workbooks.Open pstrFilePath

(missed the "r" in "pstrFilePath")

A couple of other things:

If you used "Option Explicit", it would have caught this. Although, using
it requires you to declare all of your variables (dim varibleName as
dataType). If you don't declare your variables all the time, I would
suggest that you do and that you use option explicit. It is good form and
there is a very good chance that it will save a lot of time in the future.
In my example, I declared one variable (pstrFilePath) and then, because of a
typo, tried to use another (pstFilePath). When VBA/XL tried to execute
this, it actually created both variables. If you have a small typo in many
lines of code, it might be difficult to catch. Using Option Explicit
prevents this, but requires all variables declared before using them.

We don't have anything in there to catch errors...say a user clicking the
cancel button on the GetOpenFile dialog box. If you go through the process,
but decide you don't want to continue and click the cancel button, XL will
continue and try to open the workbook named "False.xls". You might consider
putting a test in there to deal with clicking the cancel button. Something
like this:

Sub testing()
Dim pstrFilePath As String
pstrFilePath = Application.GetOpenFilename

If pstrFilePath = "False" Then
MsgBox "Canceled by user", vbInformation, "Canceled"
Exit Sub
End If

Application.Workbooks.Open pstrFilePath

End Sub

Sorry for the typo. HTH,

Conan

On Jan 30, 4:20 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:

> beginner1,
>
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Reply to this Message

beginner1.mat@hotmail.com - 31 Jan 2008 02:58 GMT

On Jan 30, 6:22 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:

> beginner1,
>
[quoted text clipped - 107 lines]
>
> - Show quoted text -

This worked perfectly!

Now that I have the file open, is there a way to SAVEAS with the
current path and filename, but adding something like "_graphs" at the
end.

So open file
file1.xls
perform macros
saveas file1_graphs.xls

Thank you for all the help.

Reply to this Message

Conan Kelly - 31 Jan 2008 03:39 GMT

beginner1,

Add the following line near the end of your macro:

ActiveWorkbook.SaveAs ActiveWorkbook.Path & Application.PathSeparator &
Replace(ActiveWorkbook.Name, ".xls", "_graphs.xls")

As long as you are working with *.xls files, this should add "_graphs" on to
the end of the file name and save it in the same location as the original
file.

HTH,

Conan

On Jan 30, 6:22 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:

> beginner1,
>
[quoted text clipped - 111 lines]
>
> - Show quoted text -

Reply to this Message