How to open a workbooks with the path file?-CodePudding

I am struggling with Workbooks(). In the below code I want the user to pick the file and get the path file from the dialogbox. All these steps are working. However I am struggling the use pathKeys. It seems when I write Workbooks(pathkeys) I have an error 9 (Script our of range).

Sub getData()

    Dim diagBoxkeys As FileDialog
    Dim pathKeys As String
    
    Set diagBoxkeys = Application.FileDialog(msoFileDialogFilePicker)
    diagBoxkeys.Title = "Keys File " & FileType
    diagBoxkeys.Filters.Clear
    diagBoxkeys.Show
    If diagBoxkeys.SelectedItems.Count = 1 Then
        pathKeys = diagBoxkeys.SelectedItems(1)
    End If
  
    MsgBox (pathKeys)
    Dim wbKeys As Workbook
    ScreenUpdating = False
 
    Set wbKeys = GetObject(pathKeys)
    Workbooks(pathKeys).Worksheets(1).Columns(2).Copy Destination:=Workbooks("Macro_PORTAL_APRR.xlsm").Worksheets(1).Columns(1)  
    wbKeys.Close Savechanges:=False
 
End Sub

However when in this code I replace Workbooks(pathKeys) with Workbooks("Keys_2021-12-27_13_43_21_utf-8.csv") it works perfectly.

I don't understand why pathKeys is not accepted as pathKeys = C:\Users\tn5809\Documents\PROJETS\PORTAL_APRR\Keys_2021-12-27_13_43_21_utf-8.csv

What am I doing wrong ?

CodePudding user response：

This might work better for you.

'This should appear at the top of all modules.
'It forces you to declare all variables.
'Tools ~ Options ~ Require Variable Declaration.
Option Explicit

Public Sub GetData()
    
    '*** If Macro_Portal is not file containing this code: ***
    Dim MacroPortalPath As String
    MacroPortalPath = OpenFile ' or MacroPortalPath = "C:\...\...\Macro_PORTAL_APRR.xlsm"
    Dim MacroPortal As Workbook
    MacroPortal = Workbooks.Open(MacroPortalPath)
    '*** If Macro Portal is the file containing this code: ***
    'Dim MacroPortal As Workbook
    'Set MacroPortal = ThisWorkbook
    
    Dim pathKeys As String
    pathKeys = OpenFile
    
    If pathKeys <> "" Then
        Dim wrkBk As Workbook
        Set wrkBk = Workbooks.Open(pathKeys)
        
        'Best to use sheet name rather than where it is in workbook (can be moved by user).
        'You could replace MacroPortal with ThisWorkbook and remove first block of code in this procedure.
        wrkBk.Worksheets("Sheet1").columns2.Copy Destination:=MacroPortal.Worksheets("Sheet1").Column(1)
        wrkBk.Close SaveChanges:=False
        
    End If
    
End Sub

Public Function OpenFile() As String

    Dim dialogBox As FileDialog
    Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)
    
    With dialogBox
        .Title = "Keys File"
        .AllowMultiSelect = False
        .InitialFileName = "C:\"
        If .Show = -1 Then
            OpenFile = .SelectedItems(1)
        End If
    End With

End Function