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?

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





wrote in message
...
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?

On Jan 30, 4:20*pm, "Conan Kelly"
wrote:
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

wrote in message

...



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?- Hide quoted text -

- 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?

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





wrote in message
...
On Jan 30, 4:20 pm, "Conan Kelly"
wrote:
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

wrote in message

...



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?- Hide
quoted text -

- 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?

On Jan 30, 6:22*pm, "Conan Kelly"
wrote:
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

wrote in message

...
On Jan 30, 4:20 pm, "Conan Kelly"





wrote:
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

wrote in message

...

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?- Hide
quoted text -

- 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?- Hide quoted text -

- 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.

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




wrote in message
...
On Jan 30, 6:22 pm, "Conan Kelly"
wrote:
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

wrote in message

...
On Jan 30, 4:20 pm, "Conan Kelly"





wrote:
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

wrote in message

...

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?- Hide
quoted text -

- 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?- Hide quoted text -

- 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.