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

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: http://www.thecodecage.com/forumz/sh...d.php?t=164934

Microsoft Office Help

Thanks so much for your help, Merry Christmas!

"joel" wrote:


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: http://www.thecodecage.com/forumz/sh...d.php?t=164934

Microsoft Office Help

.

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


"joel" wrote:


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: http://www.thecodecage.com/forumz/sh...d.php?t=164934

Microsoft Office Help

.

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" wrote:


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
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=164934)

'Microsoft Office Help' (http://www.thecodecage.com)

.



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

Microsoft Office Help

I tried it and could not get it to work.

"joel" wrote:


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" wrote:


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
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=164934)

'Microsoft Office Help' (http://www.thecodecage.com)

.



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

Microsoft Office Help

.

Can yo be more specific? I gave answered a few diferent questions so can
yo utell me which solutions didn't work and what the error messages are
for each (including the line of code that failed).


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

Microsoft Office Help

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

"joel" wrote:


Can yo be more specific? I gave answered a few diferent questions so can
yo utell me which solutions didn't work and what the error messages are
for each (including the line of code that failed).


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

Microsoft Office Help

.

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

"joel" wrote:


Can yo be more specific? I gave answered a few diferent questions so can
yo utell me which solutions didn't work and what the error messages are
for each (including the line of code that failed).


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

Microsoft Office Help

.

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

"Brian" wrote:

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

Below is the code to set the initial filename. What error messages are
you getting on the other problems. show me your code.





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

Private Sub Open_New_Engineer_Spec_8_Click()


Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
.AllowMultiSelect = False

'Add a filter that includes GIF and JPEG images and make it the
'second item in the list.
.Filters.Add "Excel Files (*.xlsm)", "*.xlsm", 1

'Sets the initial file filter to number 2.
.FilterIndex = 1

'Set the initial path to the C:\ drive.
.InitialFileName = "C:\temp\Master Engineering Spec*.xlsm"

'Use the Show method to display the File Picker dialog box and return
'the user's action.
'If the user presses the action button...
If .Show = -1 Then

FName = .SelectedItems.Item(1)
'If the user presses Cancel...
Else
MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

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

End Sub



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


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

Microsoft Office Help