0

I make a 4-weekly invoice per customer with the storage costs from the 4 past weeks. the warehouse sends me the 4 weeks from which I make a invoice

If no freight is present, no Excel document will be created for that week.

Now I want to use a macro to first search the file to see if there is a file with specific name. If not, he pastes a "–".

I have created a fictitious customer for privacy reasons but the process is exactly the same My code is as follows:

Windows("periode 1 factuur ABC123.xlsx").Activate
Range("B11").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R12C2"
Range("B14").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R14C2"
Range("B15").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R15C2"
Range("B17").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R17C2"
Range("B19").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R19C2"
Range("B20").Select
ActiveWindow.Close
Application.Left = -5
Application.Top = -5
Application.Width = 1452
Application.Height = 792
Sheets("factuur").Select
Windows("periode 1 factuur ABC123.xlsx").Activate
Range("C11").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R12C2"
Range("C14").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R14C2"
Range("C15").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R15C2"
Range("C17").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R17C2"
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C19").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R19C2"
Range("C20").Select
Windows("periode 1 factuur ABC123.xlsx").Activate
Windows("ABC123 week 4-2021.xlsx").Activate
Sheets("factuur").Select
Windows("periode 1 factuur ABC123.xlsx").Activate
Range("E11").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R12C2"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R14C2"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R15C2"
Range("E17").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R17C2"
Range("E19").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R19C2"
Range("E20").Select
Thom Haasert
  • 119
  • 7
  • 1
    1) You will benefit greatly from reading on [how to avoid using select/activate](https://stackoverflow.com/a/10717999/15597936), 2) What do you mean by _search the file_? If you want to check if the file exist in a drive, you can use `Dir("full file path")`. There are also many answers for this topic so please google for it. – Raymond Wu Oct 04 '21 at 14:37
  • Hi thanks for your reply, week 3 is missing so it's not in the macro. What I would like is that there is an option that the macro will look if the file I'am asking for is there. if not do nothing and goes further on in the macro – Thom Haasert Oct 04 '21 at 15:27
  • You aren't really answering my question, your code hints that you have already opened the workbooks then run the macro so based on your example, is your logic supposed to be "check if the week 3 workbook is open, if yes then do week 3's code else skip and move on the next week's code"? – Raymond Wu Oct 04 '21 at 16:08

0 Answers0