Opening Workbook from User Form

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a User Form that has a control Button for opening exsisiting Workbooks.

How can I get the File Open Screen to open when the Control Button is
Clicked. Is there anyway to get the same Open File Screen that Excel uses to
open? Where the user can pick the location and file to open.


Control Button name is "Open_Exsisiting_Job_Folder_Label_9"

Thanks
 
How would I do the same thing with the file name as varible?

Control Button name is "Open_Existing_Job_Folder_Label_9"
Open ??.xls file?

Also how can I setup the Print Control Button to use the Print dialog box
that excel uses, where the user can pick the printer and settings.?

Is it possible to set the File Save as File Name from User Form Box Names?
Example:
"TEO_No_1 CLLI_Code_1 CES_No_1 TEO_Appx_No_2.xls"

Thanks Again
 
I tried it and could not get it to work.

joel said:
How would I do the same thing with the file name as varible?

Control Button name is "Open_Existing_Job_Folder_Label_9"
Open ??.xls file?


Code:
--------------------


Private Sub Open_Exsisiting_Job_Folder_Label_9_Click()
Folder = "c:\temp\"

FName = Dir(Folder & "*.xls")
do while FName <> ""
Set bk = Workbooks.Open(Filename:=Folder & FName)

bk.close savechanges:=false
FName = dir()
loop

End Sub


--------------------



Also how can I setup the Print Control Button to use the Print dialog
box
that excel uses, where the user can pick the printer and settings.?


Code:
--------------------

Set printdialog = Application.Dialogs(xlDialogPrint)
dlgAnswer = printdialog.Show

--------------------



Is it possible to set the File Save as File Name from User Form Box
Names?
Example:
"TEO_No_1 CLLI_Code_1 CES_No_1 TEO_Appx_No_2.xls"


Code:
--------------------

set bk = thisworkbook
bk.saveas filename:=TEO_No_1.value
bk.saveas filename:=CLLI_Code_1.value
bk.saveas filename:=CES_No_1.value
bk.saveas filename:=TEO_Appx_No_2.xls.value 'while do you have .xls?


I would put the folder name like this
Folder = "c:\temp\"
set bk = thisworkbook
bk.saveas filename:=Folder & TEO_No_1.value
bk.saveas filename:=Folder & CLLI_Code_1.value
bk.saveas filename:=Folder & CES_No_1.value
bk.saveas filename:=Folder & TEO_Appx_No_2.xls.value 'while do you have .xls?





--------------------


Thanks Again


joel said:
Private Sub Open_Exsisiting_Job_Folder_Label_9_Click()

fileToOPen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOPen = False Then
MsgBox ("Cannot Open file - exiting Macro")
Exit Sub
End If

Set bk = Workbooks.Open(Filename:=fileToOPen)


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: 'Opening Workbook from User Form - The Code Cage
Click to expand...
Forums' (http://www.thecodecage.com/forumz/showthread.php?t=164934)
'Microsoft Office Help' (http://www.thecodecage.com)

.
Click to expand...


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=164934

Microsoft Office Help

.
Click to expand...
 
Compile Error: Method or data member not found

' Save Engineering Spec 11 Control Button

Private Sub Save_Engineering_Spec_11_Click() (Error Message is Here)

Folder = "c:\Tech\"
Set bk = ThisWorkbook
bk.SaveAs Filename:=Folder & TEO_No_1.Value
bk.SaveAs Filename:=Folder & CLLI_Code_1.Value
bk.SaveAs Filename:=Folder & CES_No_1.Value
bk.SaveAs Filename:=Folder & TEO_Appx_No_2.xls.Value 'while do you have
..xls?

End Sub
 
In this line of code I only want to retrieve the WorkBook ("Master
Engineering Spec.xlsm". It shows all file with the xlsm extension. Can I
narroww it down to only Show the Document "Master Engineering Spec.xlsm"?

' Open New Engineer Spec 8 Control Button

Private Sub Open_New_Engineer_Spec_8_Click()

FileToOpen = Application.GetOpenFilename("Master Engineering
Spec(*.xlsm), *.xlsm")

If FileToOpen = False Then

MsgBox ("Cannot Open File")

Exit Sub

End If

Set bk = Workbooks.Open(Filename:=FileToOpen)

End Sub
 
I got the User Message to work, but still can't get the Open or save to Work.
Please help me figure this out.
 
Back
Top