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

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows
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
Rich (BB code):
   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
Rich (BB code):
   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
Rich (BB code):
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
 

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows
"Object variable of With block variable not set" '91' This error i got. It looks like it would not open the file. I don't see anything in immediate.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
You have moved exit sub before else. Swap these two lines.
It seems dir finds no file or the path is wrong.
Why do you add an empty string in myfolder after thismonth?
 

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I removed This Month, now i dont have nay bug but when i run code its nothing happend its ending work on exit sube and back to beginnig

Rich (BB code):
ThisMonth = Format(Date, "mmmm")
MyFolder = "C:\Users\V1410190\Documents\FOLDER"

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

If MyFile <> "" Then

Set data_wb = Workbooks.Open(MyFile, UpdateLinks:=0)
Else
Exit Sub
End If
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Double and triple check your path and filename.
Try to open the workbook directly from the immediate window using:
VBA Code:
 application.workbooks.open yourpath\filename
Isee what happens.
Aargh, I am just wondering but cannot check immediately: does Dir return only the filename? you have to check the value of MyFile assigned by dir.
Still, the sub exiting without doing anything means that the file is not found. So read the first line in my post again.
 

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Double and triple check your path and filename.
Try to open the workbook directly from the immediate window using:
VBA Code:
 application.workbooks.open yourpath\filename
Isee what happens.
Aargh, I am just wondering but cannot check immediately: does Dir return only the filename? you have to check the value of MyFile assigned by dir.
Still, the sub exiting without doing anything means that the file is not found. So read the first line in my post again.
Application.Workbooks.Open by this normaly open file . So maybe i should remove Dir ?
 

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Its resolved. That should be .
Rich (BB code):
Set data_wb = Workbooks.Open(MyFolder & "\" & MyFile, UpdateLinks:=0)
 

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

But when i tried add This Month its stop working ... :D maybe you know why ? how to should be add in MyFolder ?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
But when i tried add This Month its stop working ... :D maybe you know why ? how to should be add in MyFolder ?
You're the only one who can answer this. I don't know your folder structure. Obviously the month is not a folder, or the file you look for is not in it. Or Maybe the month is a part of the file name. I can only keep on guessing without more details.
One thing is certain: the path to your filename is not correct
 

Palucci

Board Regular
Joined
Sep 15, 2021
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I would like it more when the file is old, e.g. from the previous month. It showed me the information "The file is old" and
 

Forum statistics

Threads
1,148,190
Messages
5,745,246
Members
423,938
Latest member
manilaphilips

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top