Macro syntax

[alainfranco]

Hello,

I am trying to set a variable that I can use throughout my macro. The variable is the filename with its associated path.

So far, Ive been succesfull in setting the variable and using it in certain objects, but not in others. The code is as follows:


Sub ScorecardImporter()
Dim SelectedFile As String
Dim ProjectClient As String


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False"


If .Show = -1 Then
SelectedFile = .SelectedItems(1)
Else
End If
End With




Workbooks.Open (SelectedFile)


Windows("SelectedFile").Activate
Worksheets("SelectedFile").Activate
Range("B3:C3").Select
Selection.Copy
.....


The variable works well with "Workbooks.Open (SelectedFile)", however I get an error with the Windows and Worksheets objects....

Would anyone have a clue as to why this is not working?

Any help would be appreciated,

Thanks!

 

[Trebor76]

Hi alainfranco,

The error is because the SelectedFile variable comprises both the path and filename where once a workbook is open you select it only by its name.

To use your original code you need to extract the filename from the path and then select it like so:

Option Explicit
Sub ScorecardImporter()
    
    Dim SelectedFile As String
    Dim ProjectClient As String
    Dim strSelectedFileName As String
    Dim objFSO As Object
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            SelectedFile = .SelectedItems(1)
        End If
    End With
    
    Workbooks.Open (SelectedFile)
    
    strSelectedFileName = objFSO.GetFileName(SelectedFile)
    
    Workbooks(strSelectedFileName).Activate

End Sub

If we introduce a workbook variable you could do it like this:

Option Explicit
Sub ScorecardImporter()
    
    Dim SelectedFile As String
    Dim ProjectClient As String
    Dim wbSelectedFileName As Workbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            SelectedFile = .SelectedItems(1)
        End If
    End With
    
    Set wbSelectedFileName = Workbooks.Open(SelectedFile)
    
    wbSelectedFileName.Activate
    
End Sub

You still have to select which tab you want to copy the data from.

HTH

Robert

 

[alainfranco]

Hi alainfranco,

The error is because the SelectedFile variable comprises both the path and filename where once a workbook is open you select it only by its name.

To use your original code you need to extract the filename from the path and then select it like so:

Option Explicit
Sub ScorecardImporter()
    
    Dim SelectedFile As String
    Dim ProjectClient As String
    Dim strSelectedFileName As String
    Dim objFSO As Object
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            SelectedFile = .SelectedItems(1)
        End If
    End With
    
    Workbooks.Open (SelectedFile)
    
    strSelectedFileName = objFSO.GetFileName(SelectedFile)
    
    Workbooks(strSelectedFileName).Activate

End Sub

If we introduce a workbook variable you could do it like this:

Option Explicit
Sub ScorecardImporter()
    
    Dim SelectedFile As String
    Dim ProjectClient As String
    Dim wbSelectedFileName As Workbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            SelectedFile = .SelectedItems(1)
        End If
    End With
    
    Set wbSelectedFileName = Workbooks.Open(SelectedFile)
    
    wbSelectedFileName.Activate
    
End Sub

You still have to select which tab you want to copy the data from.

HTH

Robert

Ah. Yes.

Many thanks Robert. Works like a charm.

Much appreciated, friend.

 

[Trebor76]

Thanks for letting us know and I'm glad it all worked out