can a file obtained using fso be set to a worbook?

[jxb]

to all
I am using fso object to obtain a file choice from the user. The file will always be an excel workbook
I need to copy -paste date form the selected xls file to another one (easy stuff)
but the question is: How can I SET this file to a workbook?

Thanks
John

 

[rlv01]

I am using fso object to obtain a file choice from the user.

What do you mean by that? Perhaps you can post your code showing how you are using FSO to obtain user input about a file.

 

[jxb]

I asked the question while working on it- just snipped to test a few things. See if the following makes sense.

        sFileSelected = SelectAfile("File choice", "*.xls;*.xlsx", "contain the data")
        'the function just use the normal fso call
            'normal use would be to:     Set file = fso.OpenTextFile(sFileToProcess, 1)
           
'but i want to do
    Dim wbkSource As Workbook 
    Set wbkSource = Workbooks.Open("thefullpathwithname")

    'so i can use the workbook object as normal

 

[rlv01]

SelectAfile("File choice", "*.xls;*.xlsx", "contain the data")

SelectAfile is not an FSO method or VBA function (VBScript perhaps?). If you want to let the user browse for a file, use the Application.GetFileName function or Application.FileDialog

Sub FileOpenTest1()
    Dim FilePathName As String
    Dim wbkSource As Workbook
    
    FilePathName = Application.GetOpenFilename("Excel Files (*.xls?), *.xls?", 1, "File Choice")
    
    If FilePathName = "False" Then
        FilePathName = ""
        MsgBox "User Cancelled"
        Exit Sub
    End If

Set wbkSource = Workbooks.Open(FilePathName)
End Sub

Sub FileOpenTest2()
    Dim PickCancelled As Boolean
    Dim FilePathName As String
    Dim wbkSource As Workbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*", 1
        .Title = "File Choice"
        .AllowMultiSelect = False
        
        PickCancelled = Not CBool(.Show)
        
        If PickCancelled Then
            FilePathName = ""
            MsgBox "User Cancelled"
            Exit Sub
        Else
            FilePathName = .SelectedItems(1)
        End If
    End With
    
    Set wbkSource = Workbooks.Open(FilePathName)
End Sub

 

[jxb]

SelectAfile("File choice", "*.xls;*.xlsx", "contain the data")

SelectAfile is not an FSO method or VBA function (VBScript perhaps?). If you want to let the user browse for a file, use the Application.GetFileName function or Application.FileDialog

Sub FileOpenTest1()
    Dim FilePathName As String
    Dim wbkSource As Workbook
  
    FilePathName = Application.GetOpenFilename("Excel Files (*.xls?), *.xls?", 1, "File Choice")
  
    If FilePathName = "False" Then
        FilePathName = ""
        MsgBox "User Cancelled"
        Exit Sub
    End If

Set wbkSource = Workbooks.Open(FilePathName)
End Sub

Sub FileOpenTest2()
    Dim PickCancelled As Boolean
    Dim FilePathName As String
    Dim wbkSource As Workbook
  
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*", 1
        .Title = "File Choice"
        .AllowMultiSelect = False
      
        PickCancelled = Not CBool(.Show)
      
        If PickCancelled Then
            FilePathName = ""
            MsgBox "User Cancelled"
            Exit Sub
        Else
            FilePathName = .SelectedItems(1)
        End If
    End With
  
    Set wbkSource = Workbooks.Open(FilePathName)
End Sub

Thanks
SelectAFile() is a function doing all the stuff you show between the With ... End with lines