PDA

View Full Version : [SOLVED] Using Workbook from FilePicker



CodesiriuS
12-20-2016, 02:36 PM
Hello I have a user form with two command buttons and a text box - The user selects the first command button and and it brings up the c: directory so the user can get a file - once the file is selected it drops the path in a text box - (this part works fine) the problem is I'm trying to get my command button 2 (named process file) to apply my macro to the selected file in the textbox. so far I can only get the macro to work on the active worksheet - Can anybody assist? currently I get a script out of range error - on this line

-----Set wThat = Workbooks(TextBox1.Value)-----

Private Sub CommandButton1_Click()
Dim SelectedFile As String

With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select required files"
.AllowMultiSelect = False
.InitialFileName = "Computer:"
.Filters.Clear
.Filters.Add "Excel Documents", "*.Xlsx", 1
.Filters.Add "Excel Documents", "*.Xls", 1
If .Show Then
SelectedFile = .SelectedItems(1)
Me.TextBox1 = SelectedFile
Else
MsgBox "User cancelled." & vbLf & vbLf & _
"Processing terminated."
Exit Sub
End If
End With
End Sub


Private Sub CommandButton2_Click()


Dim ws As Worksheet
Dim wThis As Workbook
Dim wThat As Workbook


Set wThis = ThisWorkbook
Set wThat = Workbooks(TextBox1.Value)

Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Call Cleanup(ws)
Next
Application.ScreenUpdating = True
End Sub

SamT
12-20-2016, 03:39 PM
Try opening the file before you do anything with it.

CodesiriuS
12-20-2016, 04:09 PM
Still out of range

SamT
12-20-2016, 05:14 PM
TextBox1 is probably the full path and file name

When you "Set wThat," you should only use the Filename part.

CodesiriuS
12-20-2016, 05:46 PM
Thanks Sam that worked!