VB macro for Excel to open a file and save it's name in a variable for later use

Hi Amit,
Dave is probably asleep now, so I will try to help you in his absence. The points are his though, please.

To adapt to a csv file, just change the .xls to .csv
Fname = Application.GetOpenFilename("Excel-files,*.csv", , "Please open your file")

To refer to the file elsewhere, use
Workbooks(Fname)

Fname is declared as Public, and so is available to all of your other subs and functions.

Cheers!

Brad

Amit,
I prefer:
1) to keep workbooks in a single window rather than deal with two windows
2) use two workbook variables, wb and wb2 which I set as soon the workbook is opened
3) use With...End With blocks to make sure I am referring to the proper workbook

If I had to select a range (and I would work very hard to avoid it), I would do so like:

Dim wb as Workbook
Dim ws as Worksheet
Workbooks.Open FName
Set wb=ActiveWorkbook
Set ws=ActiveSheet
'Your code
wb.Activate    'Activates a particular workbook
ws.Activate     'Activates a particular worksheet
Columns("P:Q").Select     'Select columns P & Q on that worksheet and workbook

Brad

Thanks for the grade Amit and thanks for the additional assistance Brad, much appreciated.

Cheers

Dave

Amit,
This is what I had in mind:

'This function goes in regular module sheet:
Function GetFileName(str As String) As String
Dim i As Long
If InStr(1, str, "\") = 0 Then
    GetFileName = "False"
    Exit Function
End If
For i = Len(str) To 1 Step -1
    If Mid(str, i, 1) = "\" Then
        GetFileName = Right(str, Len(str) - i)
        Exit Function
    End If
Next i
End Function


'Your sub in Sheet1 code pane
Sub Vlookupp()  'It's not good practice to use a keyword as the name of a sub, function or variable

           
' User workbook
    Dim wb As Workbook, wb2 As Workbook
    Dim fName As String, fName2 As String
    Dim ws As Worksheet, ws2 As Worksheet
    Dim lRows As Long, lRows2 As Long
   
    fName = GetFileName(Application.GetOpenFilename("Excel-files,*.xls", , "Please open your file"))
    'no file selected
           If fName = "False" Then Exit Sub
    On Error Resume Next
    Workbooks(fName).Activate
    If Err <> 0 Then
        Workbooks.Open (fName)
        Err.Clear
    End If
    On Error GoTo 0
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
   
'OpenCompAnalysisPortfolio
    fName2 = GetFileName(Application.GetOpenFilename("Excel-files,*.xls", , "Please open your Comp Analysis Portfolio"))
    'no file selected
    If fName2 = "False" Then Exit Sub
    On Error Resume Next
    Workbooks(fName).Activate
    If Err <> 0 Then
        Workbooks.Open (fName2)
        Err.Clear
    End If
    On Error GoTo 0
    Set wb2 = ActiveWorkbook
    Set ws2 = ActiveSheet
 
    lRows = ws.Cells(Rows.Count, 1).End(xlUp).Row 'change 1 to your column number
    lRows2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row 'change 1 to your column number
     
' Read the last row in the file
     
' Move columns in comp analysis portfolio

'    wb2.ActiveSheet(Cells(1, 1)).Select
    'Cells(1, 1).Select
 Windows(wb.Name).Activate
    ws.Range("P:Q").Copy    'This doesn't look too useful without a Paste Special
     Columns("A:A").Insert

' Vlook up
    Windows("HPAWW-Macro_ver3.xls").Activate     'I get error here because no file by that name
'Balance of your code not debugged

Amit,
Not enough testing. Try this instead:

Sub Vlookupp()  'It's not good practice to use a keyword as the name of a sub, function or variable
           
' User workbook
    Dim wb As Workbook, wb2 As Workbook
    Dim fName As String, fName2 As String, str As String
    Dim ws As Worksheet, ws2 As Worksheet
    Dim lRows As Long, lRows2 As Long
   
    str = Application.GetOpenFilename("Excel-files,*.xls", , "Please open your file")
    fName = GetFileName(str)
    'no file selected
           If fName = "False" Then Exit Sub
    On Error Resume Next
    Workbooks(fName).Activate
    If Err <> 0 Then
        Workbooks.Open (str)
        Err.Clear
    End If
    On Error GoTo 0
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
   
'OpenCompAnalysisPortfolio
    str = Application.GetOpenFilename("Excel-files,*.xls", , "Please open your Comp Analysis Portfolio")
    fName2 = GetFileName(str)
    'no file selected
    If fName2 = "False" Then Exit Sub
    On Error Resume Next
    Workbooks(fName2).Activate
    If Err <> 0 Then
        Workbooks.Open (str)
        Err.Clear
    End If
    On Error GoTo 0
    Set wb2 = ActiveWorkbook
    Set ws2 = ActiveSheet
 
    lRows = ws.Cells(Rows.Count, 1).End(xlUp).Row 'change 1 to your column number
    lRows2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row 'change 1 to your column number
     
' Read the last row in the file
   
   
' Move columns in comp analysis portfolio

'    wb2.ActiveSheet(Cells(1, 1)).Select
    'Cells(1, 1).Select
 Windows(wb.Name).Activate
    ws.Range("P:Q").Copy    'This doesn't look too useful without a Paste Special
    'Range(Cells(1, 16), Cells(lRows2, 16)).Select
    'Selection.Cut
    'Application.CutCopyMode = False
    'Selection.Copy
    Columns("A:A").Insert

' Vlook up
    Windows("HPAWW-Macro_ver3.xls").Activate
'Your code follows here

Brad