VBA to open files in a folder - problem with open file

[Palucci]

I have a macro, I would like to replace the code that opens with diagbox to the one that opens automatically if there is a file with a date from this month in the folder. But here

   data_wb.Sheets("Year2021").Rows("1:1").Select

i got error like : "Object raiable or With block wariable not set '91' " How i can change below

Below is code which i would like adapt

   ThisMonth = Format(Date, "mmmm")
   MyFolder = "C:\Users\V1410190\Documents\FOLDER" & ThisMonth & ""
   MyFile = Dir(MyFolder & "\CopyFinakl*.xlsm")
   Do Until MyFile = ""
   MyFile = Dir
Set data_wb = Workbooks.Open(MyFile, UpdateLinks:=0)

Loop


  
   'paste copy like value and change to date format'
   data_wb.Sheets("Year2021").Rows("1:1").Select

Here what i had and still have

file_name = selectFilePK
If file_name = "" Then Exit Sub

'Set data file
Set data_wb = Workbooks.Open(file_name, UpdateLinks:=0)
Private Function selectFilePK()
    Dim fd As Office.FileDialog

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .InitialFileName = ActiveWorkbook.Path
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel", "*.xlsm"

        If .Show = True Then selectFilePK = .SelectedItems(1)
        Application.ScreenUpdating = False
        Application.AskToUpdateLinks = False

 

[bobsan42]

MyFile = Dir

Set data_wb = Workbooks.Open(MyFile, UpdateLinks:=0)

Swap these two lines.
However this will only give you the last file in the folder. But you will open all of them in the process.
How many files will you have in the folder? Which one do you want to open?

 

[Palucci]

What if I would like it to always select this file by name myfile? No matter how many files there are in the folder

 

[bobsan42]

If you only have 1 file or just open the first one use this:

MyFile = Dir(MyFolder & "\CopyFinakl*.xlsm")

If MyFile <> "" then

Set data_wb = Workbooks.Open(MyFile, UpdateLinks:=0)

Else

Exit sub

end if

 

[Palucci]

Ok, if I understand correctly, this code will let me run if I only have one file in a folder. And if I have several files, but of all of them, I'm only interested in this one: CopyFinakl.xlsm

 

[bobsan42]

The procedure will exit if it doesn't find a file like CopyFinakl*.xlsm.
If you are looking for a particular filename remove the asterisk or provide the filename when you use Dir.

 

[Palucci]

Still im getting error here : data_wb.Sheets("Adekwatnosc").Rows("1:1").Select . What i wrote wrong ?

ThisMonth = Format(Date, "mmmm")
MyFolder = "C:\Users\V1410190\Documents\FOLDER" & ThisMonth & ""
MyFile = Dir(MyFolder & "\CopyFinakl*.xlsm")

If MyFile <> "" Then

Set data_wb = Workbooks.Open(MyFile, UpdateLinks:=0)

Else
Exit Sub
End If

 

[bobsan42]

What is the error?
What is data_wb?
Try like this also:

.Rows(1).EntireRow.Select

 

[Palucci]

This is all my code data_wb is a file which i try open and working there by macro . Find there some cell by inputbox copy and past on wbMe

Dim vDate As Date
Dim wbMe As Workbook
Dim data_wb As Workbook
Dim ws As Worksheet
Dim inputbx As String
Dim loc As Range, lc As Long
Dim MyFolder As String, ThisMonth As String
Dim MyFile As String

'Set workbook'

Set wbMe = ActiveWorkbook
With wbMe.Sheets("input_forecast").Rows("1:1")
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    .NumberFormat = "YYYY-MM-DD"
  End With
  
Application.ThisWorkbook.UpdateLinks = xlUpdateLinksNever '2
Application.DisplayAlerts = False
  '''''
  
ThisMonth = Format(Date, "mmmm")
MyFolder = "C:\Users\V1410190\Documents\FOLDER" & ThisMonth & ""
MyFile = Dir(MyFolder & "\CopyFinakl*.xlsm")

If MyFile <> "" Then

Set data_wb = Workbooks.Open(MyFile, UpdateLinks:=0)

Exit sub
Else
End If

   'paste copy like value and change to date format'
   data_wb.Sheets("Adekwatnosc").Rows("1:1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "YYYY-MM-DD"
 
 Do
        inputbx = InputBox("Enter Date, FORMAT; YYYY-MM-DD", , Format(VBA.Now, "YYYY-MM-DD"))
        If inputbx = vbNullString Then Exit Sub
        On Error Resume Next
        vDate = DateValue(inputbx)
        On Error GoTo 0
        DateIsValid = IsDate(vDate)
        If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
    Loop Until DateIsValid
       

     

     data_wb.Worksheets("Adekwatnosc").Activate
     With data_wb.Worksheets("Adekwatnosc")
        Set loc = .Cells.Find(what:=vDate)
      If Not loc Is Nothing Then
            lc = .Cells(loc.Row, Columns.Count).End(xlToLeft).Column
            .Range(.Cells(109, loc.Column), .Cells(123, lc)).Copy
      Set locPaste = wbMe.Sheets("input_forecast").Cells.Find(what:=vDate)
      wbMe.Sheets("input_forecast").Cells(27, locPaste.Column).PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False
        End If
    End With
data_wb.Close SaveChanges:=False
MsgBox "Done!"
End Sub

 

[bobsan42]

What i meant was:
What is the error now and what is the data_wb object at the time of the error- is it a workbook object or nothing?