Solved: Can macro let user click to choose workbook

[Meatball]

I am wondering if it is possible to have a macro in a workbook that pulls info from another workbook where the the workbook to pull from will have an unknown name so the user clicks on it as the macro runs? I beleive it might be if those are the only 2 workbooks open but what if you have more than 2 open?

[lucas]

How about the option to browse to the folder to open and select the file you wish to pull data from?

Dim wb As Workbook
Dim sFileName As String
Application.ScreenUpdating = False ' turn off the screen updating
'Show the open dialog and pass the selected _
'file name to the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(sFileName)

[Meatball]

I was thinking mainly of e-mail attacchments that would not be in a folder until after the macro is run.

[lucas]

I'm sorry. I just offered an option based on the info you provided. If you would like to fill us in on what the real, complete scenario is then maybe we can help.

From what I get in your posts you are either getting this from an attachment in outlook or you have an open workbook and and want to know how to find it.....

If the latter you could check to see if it is open and then run your code on it.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=468

[Meatball]

No problem Lucas. I guess I was a bit vague with the first post.
First everything is in spreadsheets, no databases.
When a customer contacts us to quote a price on product, we send a form to them. When they return the form, having been renamed to whatever, I open a template that holds all the data on for the request.
I take the info from the customers form, put it into my template, save the template which is named according to info from the customer, then save the customers form into the new folder with the renamed template.
What I am wanting to do is make a macro in the template. It would pull the info from the customer form, rename and save the template, and possibly rename and save the customers form into the same folder.
So my problem is that I can not have the name of the customers form in the macro.
Let me know if you need more details of my process.

[lucas]

So when they send it back to you is it not on your hard drive somewhere? or on your network? Why can't you browse to it and select the file using the code I posted?

[Meatball]

I may have misunderstood what your code does. I thought it looked in folders for the file. If it actually looks for the names of workbooks that are open then that would work because I do not save it to a folder until after I would run the macro.

[mdmackillop]

This will show a list containing open workbooks. The button can run code on the selected book.

[Meatball]

mdmackillop, I opened your attached file and was able to pull up a list of open workbooks but when I hit the comand buttun it killed my system. I had to go through ayo recovery and lost all the macros on the test workbook I am using. I also get an error about incorrect use of null. Any ideas whats wrong? And How would I install or implement this onto my own form?

[lucas]

The incorrect use of null occurs when there are no other open workbooks....

The problems you are having do not seem to have anything to do with Malcolms file at all. It must be something in your workbook that you selected.

When I run malcolms file I get a list of open workbooks and when you select one and hit the button you get a messagebox telling you the name of the file you selected.....that is all.

[Meatball]

OK I never did select a file, just saw that I had a list of what was open and then hit the command buttun. I have tested it since the malfunctions and had no trouble. I still would like to be able to basically have this to where as a macro runs it gets to a piont where the box to choose a file opens and when the file is chosen the box closes and the macro continues. I do not have the macro written yet but it would go something like this;
Open e-mail atttachment
Open my template
Start macro
At this point I would choose the file name from open workbooks
Macro would continue, copying from attachment, pasting to template, renaming and saving both workbooks.

So it could be a module of it's own, but I do not want the bottons. I would like it to close upon clicking of a file. If that is not possible a button to close would be OK

[Meatball]

Still looking for the answer that will solve this thread.

I still would like to be able to basically have this to where as a macro runs it gets to a piont where the box to choose a file opens and when the file is chosen the box closes and the macro continues. I do not have the macro written yet but it would go something like this;
Open e-mail atttachment
Open my template
Start macro
At this point I would choose the file name from open workbooks
Macro would continue, copying from attachment, pasting to template, renaming and saving both workbooks.

So it could be a module of it's own, but I do not want the bottons. I would like it to close upon clicking of a file. If that is not possible a button to close would be OK

[mdmackillop]

Remove the button from the form and add your code as a ListBox Click event.

[Meatball]

mdmackillop, I do not have experience with userforms so please be patient with me. I do not know if you are not quite getting the idea of what I want to happen or if I am misunderstanding because of my lack of knowledge.
I would like to be able to basically have this to where as a macro that is in my template runs it gets to a piont where the box to choose a file opens and when the file is chosen the box closes and the macro continues using the file that was chosen. I do not have the macro written yet but it would go something like this;
Open e-mail atttachment
Open my template,
Start macro
At this point I would choose the file name from open workbooks shown in a listbox. The listbox can show up on my template or as a msgbox.
The listbox closes or is closed.
Macro would continue, always using the file that was chosen, copying from cosen file, pasting to template, renaming and saving both workbooks.

So the listbox part of this could be a module of it's own, but I do not necessarily want the bottons. I would like it to close upon clicking of a file. If that is not possible a button to close would be OK
I can get a msgbox to show with a listbox and close button but cannot seem to get the listbox to show anything or the close button to close the window.

[Meatball]

Progress being made. I now have a macro that will open a message box which shows open workbooks, and when I click the finished button the message box closes. What I need to figure out is how to incorporate the chosen file into the rest of the macro.

[mdmackillop]

This uses the UserForm as before. Clicking on a file name runs a macro and closes the userform

[Meatball]

Thanks for all the effort mdmackillop but I actually just got it working the way I want. Codes used are

From your code, placed in module, opens userform with listbox and command button

Sub shows()
UserForm1.Show False
End Sub


from your code, lists open workbooks

Private Sub UserForm_Initialize()
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ActiveWorkbook.Name Then
ListBox1.AddItem wb.Name
End If
Next
End Sub


beginning and end parts from your code, middle part found using seach of this forum, Shows msgbox with name of chosen file and puts that name on active worksheet

Private Sub CommandButton1_Click()
MsgBox ListBox1
Dim ws As Worksheet

Set ws = Worksheets("INFO")
ws.Cells(1, 1).Value = ListBox1.Value

Unload UserForm1
End Sub


Now I can have the rest of the macro reference that cell for the workbook to deal with( I hope, haven't gotten that far yet).
Thanks again for the help

[mdmackillop]

Rather than write the name to the sheet, you can pass the value (workbook name) to your following sub routine. eg

Option Explicit
Private Sub UserForm_Initialize()
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ActiveWorkbook.Name Then
ListBox1.AddItem wb.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
UserForm1.Hide
Call DoStuff(ListBox1)
Unload UserForm1
End Sub

Sub DoStuff(wb As String)
Dim MySheet As Worksheet
Set MySheet = Workbooks(wb).Sheets(1)
MsgBox MySheet.Cells(1, 1)
End Sub

[Meatball]

Thanks, I will have a look at that. Right now I am trying to get code to read the name that the userform put into my spreadsheet. If I have this
"Dim OWB as String"
"OWB = Range("A1").Value"

But i get an error, "Subscript out of Range"
at
"Windows("OWB").Activate".
I am guessing that I need to redo this line but I am not sure how when using named string, OWB. Any idea the correct way to write this?

After I have things working this way I will get into your last post, that will give me time to study and understand it.

[mdmackillop]

Can you post your whole code?

[Meatball]

Sub PullFromCustRFQ()
Dim OWB As String
OWB = Range("A1").Value

Call OpenWorkbooks

Windows("OWB").Activate
Sheets("Main Customer Info").Select
Range("B10").Select
Selection.Copy
ThisWorkbook.Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

[Meatball]

I got it, just had to change the line to
Workbooks(OWB).Activate
and make sure the proper file was actually open

I knew it had to be a simple fix. Thanks again to all for the help. I am now marking this thread as SOLVED!!!!!!

[mdmackillop]

Tyr to avoid Activating/Selecting. It allows code to be simpler and run much more quickly

Sub PullFromCustRFQ()
Dim OWB As String
OWB = Range("A1").Value

Call OpenWorkbooks


Workbooks("OWB.xls").Sheets("Main Customer Info").Range("B10").Copy
ActiveSheet.Range("B4").PasteSpecial Paste:=xlPasteValues

[Meatball]

Thanks, I can shorten the length of a couple of my macro's with that