PDA

View Full Version : Code to pick a workbook and add a code to it



TheMachine
08-07-2007, 11:10 AM
Is there a way to find all workbooks in a computer using excel, and then pick which one to add a code to it?

TheMachine
08-07-2007, 11:14 AM
and once picked, it runs the code to the selected workbook?

TheMachine
08-07-2007, 11:14 AM
and once picked, it runs the code to the selected workbook?

rory
08-08-2007, 02:00 AM
Do you mean you want to add code to a workbook, or run code that manipulates a particular workbook?

TheMachine
08-08-2007, 09:08 AM
Do you mean you want to add code to a workbook, or run code that manipulates a particular workbook?

I need a code that will let me choose a workbook, then when chosen, I can run a code I already have on it.

Bob Phillips
08-08-2007, 09:36 AM
If you add the code to Workbook_Open in that workbook, it will run automatically when you open it.

You can select a file using GetOpenFilename, look it up in Help to see how to use it.

Norie
08-08-2007, 09:38 AM
Well you can easily use GetOpenFilename to allow the user to choose a workbook.

Then you can open it, create a reference to it and use that reference in the code.

By the way your title and original post were rather confusing, I think anyway.

I actually thought you wanted to select a workbook and then use code to create code in the selected workbook.

TheMachine
08-08-2007, 10:47 AM
Well you can easily use GetOpenFilename to allow the user to choose a workbook.

Then you can open it, create a reference to it and use that reference in the code.

By the way your title and original post were rather confusing, I think anyway.

I actually thought you wanted to select a workbook and then use code to create code in the selected workbook.

Is there a way for the code to be run as soon as the user selects the workbook from GetOpenFilename?

Norie
08-08-2007, 11:08 AM
Not really, you would probably still need to open the workbook.

Is that a problem?

PS What is the code actually doing?

TheMachine
08-08-2007, 11:19 AM
Not really, you would probably still need to open the workbook.

Is that a problem?

PS What is the code actually doing?

Yeah that is a problem, I need to hypothetically think the user has no Excel VBA knowledge at all. The code just creates a toolbar that hides/unhides columns in the spreadsheet.

TheMachine
08-13-2007, 11:55 AM
Well you can easily use GetOpenFilename to allow the user to choose a workbook.

Then you can open it, create a reference to it and use that reference in the code.

By the way your title and original post were rather confusing, I think anyway.

I actually thought you wanted to select a workbook and then use code to create code in the selected workbook.

Can I get instructions on doing this?

Bob Phillips
08-13-2007, 12:42 PM
Sure, lookup up GetOpenFilename and Set in VBA help.

TheMachine
08-14-2007, 10:01 AM
I got this far:


Sure, lookup up GetOpenFilename and Set in VBA help.


Sub GetImportFilename()
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim Filename As Variant

' Set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Filed (*.csv),*.csv," & _
"ASCII FILES (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the filename
Filename = Application.GetOpenFilename(Finfo, FilterIndex, File)

' Handel return info dialog box
If Filename = False Then
MsgBox "No file was selected."
Else
MsgBox "You selected " & Filename
End If




End Sub
how do I make it open the file when I select it?

Bob Phillips
08-14-2007, 10:09 AM
Sub GetImportFilename()
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim Filename As Variant
Dim WB As Workbook

' Set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Filed (*.csv),*.csv," & _
"ASCII FILES (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the filename
Filename = Application.GetOpenFilename(Finfo, FilterIndex, file)

' Handel return info dialog box
If Filename = False Then
Exit Sub
Else
Set WB = Workbooks.Open(Filename)
End If

End Sub