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