set file path as a workbook error

[drop05]

Hi i am trying to get a user to select a file and set tht file path as a string then with that string set that as a workbook since i am trying to reference an outside workbook and worksheet but getting an error here

Set Wbl = wokrbooks.EUR_FName

Object required run time error 424

Sub get_data()

Dim OpenFileName As String, srcWS As Worksheet
EUR_FName = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")

Dim Wb1 As Workbook
Set Wbl = wokrbooks.EUR_FName
Set srcWS = Wbl.Sheets("Sheet1")

not sure what i need to do to fix this

 

[Domenic]

Try it like this...

Sub get_data()

    Dim OpenFileName As Variant
    OpenFileName = Application.GetOpenFilename( _
        FileFilter:="Excel files (*.xls*), *.xls*", _
        Title:="Select Excel file", _
        ButtonText:="Select")
        
    If OpenFileName = False Then Exit Sub
        
    Dim Wb1 As Workbook
    Set Wb1 = Workbooks.Open(OpenFileName)
    
    Dim srcWS As Worksheet
    Set srcWS = Wb1.Sheets("Sheet1")
    
End Sub

Hope this helps!

 

[drop05]

Try it like this...

Sub get_data()

    Dim OpenFileName As Variant
    OpenFileName = Application.GetOpenFilename( _
        FileFilter:="Excel files (*.xls*), *.xls*", _
        Title:="Select Excel file", _
        ButtonText:="Select")
       
    If OpenFileName = False Then Exit Sub
       
    Dim Wb1 As Workbook
    Set Wb1 = Workbooks.Open(OpenFileName)
   
    Dim srcWS As Worksheet
    Set srcWS = Wb1.Sheets("Sheet1")
   
End Sub

Hope this helps!

Yes this helps a lot, if you dont mind, i am trying to get the last row used from cell a3 down but getting an error on the line

LR = srcWS.range("A3" & rows.Count).End(xlUp).Row

Sub get_dataa()

    Dim OpenFileName As Variant
    Dim LR As Long
    OpenFileName = Application.GetOpenFilename( _
        FileFilter:="Excel files (*.xls*), *.xls*", _
        Title:="Select Excel file", _
        ButtonText:="Select")
        
    If OpenFileName = False Then Exit Sub
        
    Dim Wb1 As Workbook
    Set Wb1 = Workbooks.Open(OpenFileName)
    
    Dim srcWS As Worksheet
    Set srcWS = Wb1.Sheets("Sheet1")
    
    LR = srcWS.range("A3" & rows.Count).End(xlUp).Row

 

[Joe4]

You need to remove the "3" after "A3", i.e.

LR = srcWS.range("A" & Rows.Count).End(xlUp).Row

 

[drop05]

i

Yes this helps a lot, if you dont mind, i am trying to get the last row used from cell a3 down but getting an error on the line

LR = srcWS.range("A3" & rows.Count).End(xlUp).Row

Sub get_dataa()

    Dim OpenFileName As Variant
    Dim LR As Long
    OpenFileName = Application.GetOpenFilename( _
        FileFilter:="Excel files (*.xls*), *.xls*", _
        Title:="Select Excel file", _
        ButtonText:="Select")
      
    If OpenFileName = False Then Exit Sub
      
    Dim Wb1 As Workbook
    Set Wb1 = Workbooks.Open(OpenFileName)
  
    Dim srcWS As Worksheet
    Set srcWS = Wb1.Sheets("Sheet1")
  
    LR = srcWS.range("A3" & rows.Count).End(xlUp).Row

Try it like this...

Sub get_data()

    Dim OpenFileName As Variant
    OpenFileName = Application.GetOpenFilename( _
        FileFilter:="Excel files (*.xls*), *.xls*", _
        Title:="Select Excel file", _
        ButtonText:="Select")
       
    If OpenFileName = False Then Exit Sub
       
    Dim Wb1 As Workbook
    Set Wb1 = Workbooks.Open(OpenFileName)
   
    Dim srcWS As Worksheet
    Set srcWS = Wb1.Sheets("Sheet1")
   
End Sub

Hope this helps!

i noticed that the file does open, is there a way to close the file at the end? also to check if that file has filtered columns and to clear those filters?

 

[Joe4]

i noticed that the file does open, is there a way to close the file at the end?

Just use:

Wb1.Close

 

[drop05]

Just use:

Wb1.Close

Thank you! sorry for double posts again

 

[drop05]

Just use:

Wb1.Close

do you know of a way to check if that opened workbook if it has a filter then unfilter it if it does?

 

[Joe4]

do you know of a way to check if that opened workbook if it has a filter then unfilter it if it does?

You have already asked that question here and received replies: Unfilter a whole sheet
You need to stop asking questions in multiple different threads!