CODEWITHSUNDEEP
vbaexcel
tombata
tombata

Reputation: 259

VBA browse file and make operations

Hello I am trying to write a VBA macro, which browses excel files and then makes operation in the files. The code I wrote is as follows:

Option Explicit

Sub SelctFile()
Dim intChoice As Integer
Dim strPath As String
Dim i As Integer

'allow the user to select multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    For i = 1 To Application.FileDialog(msoFileDialogOpen _
        ).SelectedItems.Count
        strPath = Application.FileDialog(msoFileDialogOpen _
        ).SelectedItems(i)
        'print the file path to sheet 1
        Cells(i + 1, 2) = strPath
    Next i
End If    
End Sub


Sub ISIN()

Dim MSReport As Variant

MSReport = Range("B2").Value

Set MSReport = Workbooks.Open(Filename:="MSReport")
Range("W3:W2500").Formula = "=IF(G3="""","""",BDP(G3&"" Equity"",""ID_ISIN""))"

End Sub

The first Sub SelectFile chooses the files and I have the file path in cell B2. So I want to use the path from cell B2 in Sub ISIN.

If I write the address it works, but I want that the macro takes automatically the address.

Also is it possible that the changes are mada without opening the another worksheet.

Upvotes: 1

Views: 5294

Answers (1)

R3uK
R3uK

Reputation: 14547

You should simply use an argument in ISIN, try this! ;)

Option Explicit

Sub SelctFile()
    Dim intChoice As Integer
    Dim strPath As String
    Dim i As Integer

    'allow the user to select multiple files
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        For i = 1 To Application.FileDialog(msoFileDialogOpen).SelectedItems.Count
            strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(i)
            ISIN strPath
            ''Add other procedures there!
            'NewProcedure strPath
        Next i
    End If
End Sub


Sub ISIN(ByVal FilePath As String)
    Dim MSReport As Excel.Workbook
    Set MSReport = Workbooks.Open(Filename:=FilePath)

    MSReport.Sheets("SheetName").Range("W3:W2500").Formula = _
            "=IF(G3="""","""",BDP(G3&"" Equity"",""ID_ISIN""))"

    MSReport.Save
    MSReport.Close False
End Sub

Upvotes: 2

Related Questions