programming input box

[Guest]

I have gotten this far with programming an input box. The box asks for a
file name in which the macro does subsequent processing. I don't know the
file name; the macro will be run by someone else without macro experience so
I need to set this up as foolproof as possible.
There are three things wrong with the following code:
1. the "If temp = 2" statement doesn't work; I am assuming because this is
an input box and I haven't been able to find any help on code for this
2. the applications display alerts = false doesn't work. I don't want
there to be anymore dialogue just close down excel.
3. and three I want the user to be abel to open the excel workbook if it
is not already available.

Thanks,
Nicole

temp = InputBox("What is the name of the PRISM data Excel Workbook you want
me to format for the M101 analysis?")
If temp = 2 Then Application.Quit
If temp = "" Then
MsgBox ("You did not enter a name. Please try again.")
temp = InputBox("What is the name the workbook you want me to format for the
M101 analysis?")
If temp = "" Then
MsgBox ("Excel will now close. Have a good day.")
Application.DisplayAlerts = False
Application.Quit
End If
End If

 

[Harald Staff]

Hi Niocole

Are you sure that an inputbox is the best suited input device for this task
? If I understand what you want then you need three available choices
- select one of the open workbooks from a list;
- select a closed workbook to open in a familiar "open" dialog;
- cancel the operation.
This can easily be done with a userform. Userforms are easy and fun to make
and extremely useful.

And: Don't ever ever ever quit Excel by code. If Excel was running my budget
and you closed it without even a "save changes?" prompt then I'd never touch
your program again.

Best wishes Harald

 

[Guest]

I would suggest using the "Application.GetOpenFileName" function, rather than
in input box, since this is exactly what it was designed for. If you do some
quick searching for "GetOpenFileName" you should be able to figure out how to
use it without any problems.

Let me know if you need anything more specific than this.

 

[Guest]

Sorry if I wasn't clear.
The process is as follows:
User gets data from finance in preformatted workbook
User also downloads data from project management tool
User saves both workbooks under various and mysterious names
User runs my macro and I have to ask what the various and mysterious names
of the workbooks are because this macro automates a very long analysis
process
cutting about four days of analysis down to 30 minutes max.
So, if I quit the macro before any changes to either data base then we are
still okay.

 

[Guest]

Okay. This is a start. I went to excel help and then typed in this:
Sub TestGETOPEN()
Dim filetoopen As Variant

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen <> False Then
Workbooks.Open (filetoopen)
End If
End Sub

And, good news, it allows me to choose a file. And, the bad news, I don't
know how I would automate the macro from there?? This would not work:

Set wrkbk = Workbooks(filetoopen & ".xls"), because filetoopen captures the
entire path of the workbook.

Thanks,
Nicole

 

[Guest]

The "Workbooks.Open" function returns the workbook that is opened, so you can
use the following:

Option Explicit

Sub TestGETOPEN()
Dim filetoopen As String
Dim wrkbk As Workbook

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")

If filetoopen <> "" Then
Set wrkbk = Workbooks.Open(filetoopen)
End If
End Sub

If you want to get the filename without the entire path you can use
"wrkbk.Name"

Hope this helps,
Lucas