Variable file name and path with button

zsolti90 · Feb 23rd 2020

Hello! I have this code:

Set w1 = Workbooks("1.xlsx").Sheets("Sheet1")

Set w2 = Workbooks("2.xlsx").Sheets("Sheet1")

My poblem is, every time the 2.xlsx file name change, and i would like change it each time, possible to do with button, just browse the file? Its something simple code just copy few cells after if its match with the 1.xlsx file. Th 1.xlsx file can be fix because for this file never change the name, but the 2.xlsx filename always can be anything. I dont have too much experience with VBA, and its totally different than C# so i stuck here.

Somebody can help me please, how possible give for w2 file name and path with button? Thank you!

trunten · Feb 23rd 2020

Here's a bit of example code for opening a workbook and then doing stuff with it:

Code

Option Explicit

Function getFile() As Workbook
    Dim fn As Variant
    
    fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select workbook")
    If TypeName(fn) <> "Boolean" Then Set getFile = Workbooks.Open(fn)
End Function

Sub useGetFile()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim wb1Sheet1 As Worksheet, wb2Sheet1 As Worksheet
    
    Set wb2 = getFile
    If Not wb2 Is Nothing Then
        On Error Resume Next
        Set wb2Sheet1 = wb2.Sheets("Sheet1")
        On Error GoTo 0
        If Not wb2Sheet1 Is Nothing Then
            Set wb1 = Workbooks("1.xlsx")
            Set wb1Sheet1 = wb1.Sheets("Sheet1")
            '
            'Continue with the rest of your code....
            '....
            '....
        Else
            MsgBox "Sheet1 not found in " & wb2.Name, vbCritical
        End If
        'Maybe close wb2 here?
        wb2.Close SaveChanges:=False
    Else
        Debug.Print "User cancelled"
    End If
    Set wb1 = Nothing
    Set wb2 = Nothing
    Set wb1Sheet1 = Nothing
    Set wb2Sheet1 = Nothing
End Sub

Display More

And just in case, really simple guide on adding a button:

zsolti90 · Feb 23rd 2020

Thank you very much your help. I did something like this, but sadly its not work i get always error:

Code

Option Explicit
Function getFile() As Workbook
 Dim fn As Variant
 
 fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select workbook")
 If TypeName(fn) <> "Boolean" Then Set getFile = Workbooks.Open(fn)
End Function
Sub useGetFile()
Dim Dic As Object, key As Variant, oCell As Range, i&
 Dim wb1 As Workbook, wb2 As Workbook
 Dim wb1Sheet1 As Worksheet, wb2Sheet1 As Worksheet
 
 Set wb2 = getFile
 If Not wb2 Is Nothing Then
 On Error Resume Next
 Set wb2Sheet1 = wb2.Sheets("Sheet1")
 On Error GoTo 0
 If Not wb2Sheet1 Is Nothing Then
 Set wb1 = Workbooks("1.xlsx")
 Set wb1Sheet1 = wb1.Sheets("Sheet1")
 i = wb1.Cells.SpecialCells(xlCellTypeLastCell).Row

 For Each oCell In wb1.Range("A1:A" & i)
        If Not Dic.exists(oCell.Value) Then
            Dic.Add oCell.Value, oCell.Offset(, 3).Value
        End If
    Next

    i = wb2.Cells.SpecialCells(xlCellTypeLastCell).Row

    For Each oCell In wb2.Range("A2:A" & i)
        For Each key In Dic
            If oCell.Value = key Then
                oCell.Offset(, 2).Value = Dic(key)
            End If
        Next
    Next
    
    
 Else
 MsgBox "Sheet1 not found in " & wb2.Name, vbCritical
 End If
 'Maybe close wb2 here?
 wb2.Close SaveChanges:=False
 Else
 Debug.Print "User cancelled"
 End If
 Set wb1 = Nothing
 Set wb2 = Nothing
 Set wb1Sheet1 = Nothing
 Set wb2Sheet1 = Nothing
End Sub

Display More

trunten · Feb 23rd 2020

You're trying to access cells of a workbook. Workbooks don't have cells, only worksheets/range objects do

Slight tweak:

Code

Option Explicit

Function getFile() As Workbook
    Dim fn As Variant
    
    fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select workbook")
    If TypeName(fn) <> "Boolean" Then Set getFile = Workbooks.Open(fn)
End Function

Sub useGetFile()
    Dim Dic As Object
    dim key As Variant
    dim oCell As Range
    dim i as long 
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb2 = getFile
    If Not wb2 Is Nothing Then
        On Error Resume Next
        Set ws2 = wb2.Sheets("Sheet1")
        On Error GoTo 0
        If Not ws2 Is Nothing Then
            Set wb1 = Workbooks("1.xlsx")
            Set ws1 = wb1.Sheets("Sheet1")
            i = ws1.Cells.SpecialCells(xlCellTypeLastCell).Row
            For Each oCell In ws1.Range("A1:A" & i)
                If Not Dic.exists(oCell.Value) Then
                    Dic.Add oCell.Value, oCell.Offset(, 3).Value
                End If
            Next
            i = ws2.Cells.SpecialCells(xlCellTypeLastCell).Row
            For Each oCell In ws2.Range("A2:A" & i)
                For Each key In Dic
                    If oCell.Value = key Then
                        oCell.Offset(, 2).Value = Dic(key)
                    End If
                Next
            Next
        Else
            MsgBox "Sheet1 not found in " & wb2.Name, vbCritical
        End If
        'Maybe close wb2 here?
        wb2.Close SaveChanges:=False
    Else
        Debug.Print "User cancelled"
    End If
    Set wb1 = Nothing
    Set wb2 = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

Display More

zsolti90 · Feb 23rd 2020

I did try but i got "Object variable or With block variable not set" error.

trunten · Feb 23rd 2020

where?

zsolti90 · Feb 23rd 2020

This line:

Code

Dic.Add oCell.Value, oCell.Offset(, 3).Value

trunten · Feb 23rd 2020

Ahhhh. You haven't initialised your dictionary object.

trunten · Feb 23rd 2020

Put this under your variable declarations (The "Dim" statements):

Code

set Dic = CreateObject("Scripting.Dictionary")

And then, just above "End Sub" type this:

Code

set Dic = Nothing

zsolti90 · Feb 23rd 2020

Can you help me how can i fix it? I am on really basic level with VBA

Thank you i test it now

zsolti90 · Feb 23rd 2020

I tested and its work. Thank you really your help! I have jsut one problem. Its can not copy the cells to other excel file.

zsolti90 · Feb 23rd 2020

OMG its work. Thank you very much!

trunten · Feb 23rd 2020

no problem ?

glad i could help

Variable file name and path with button

Participate now!

Share

Tags

Users Viewing This Thread