0

I am new to vba, I want to transfer data from multiple sheets in one folder to one sheet. I wrote the programme as follows:

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow
MyFile = Dir("C:\Bulletinwork\")

Do While Len(MyFile) > 0
If MyFile = "Bmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)
Range("A4:I42").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 9))

MyFile = Dir

Loop

End Sub

Can someone help me find the reason why when I try to run the programme, I get an error message saying "sub or function not defined".

Kenny

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
kenny Hatontola
  • 1
  • Try open your files like this: directory = "Path to your files" filename = Dir(directory & "*.xl**") Do While filename <> "" Workbooks.Open (directory & filename) loop and use 'Option Explicit' right before you sub – Ionut Mar 28 '18 at 13:37
  • Also do you receive your error at a specific line or when you try to run your code? If you want me to post the code bellow let me know – Ionut Mar 28 '18 at 13:38
  • There are multiple issues with your code, but they are not related to `Sub or function not defined`. It might be about macros in the files you are opening. – GSerg Mar 28 '18 at 19:35

1 Answers1

-1

The following line will be causing some grief:

Workbooks.Open (MyFile)

The () means that VBA is trying to evaluate MyFile before running the Open command. Of course, MyFile is a string/path so can't run.

Try

Workbooks.Open MyFile

instead.

AJD
  • 2,400
  • 2
  • 12
  • 22
  • Evaluating `(MyFile)`, while unnecessary here and [harmful in certain cases](https://stackoverflow.com/q/8070033/11683), is valid, possible and yields `MyFile` as a result. – GSerg Mar 28 '18 at 19:29
  • Downvoted? This is VBA, and the use of () on a left hand call of a function/method does cause issues - I have experienced this myself - something seemingly innocuous is not passed correctly. – AJD Mar 29 '18 at 20:40
  • @GSerg: Read the additional explanation that is linked in the answer you linked - you will see that the "certain cases" is a bit broader than what you are implying. – AJD Mar 29 '18 at 20:44
  • It is *my* answer, and I added that link to it specifically because I had read the explanation it pointed to. I am saying that in this particular case `(MyFile)` is not causing any problems to the OP, and certainly not the `Sub or function not defined` error. It is correct that the parentheses must be removed. But they happen to cause no harm in this particular case, so an answer that suggests `(MyFile)` as the sole reason of the problem is wrong (and therefore should be downvoted). At most, you should have left a comment saying, "As a side note, you should remove the parentheses". – GSerg Mar 29 '18 at 21:28
  • @GSerg: In my experience, it may contribute to the overall answer.Yes, you may have different experiences. Secondly, Comments are not there to provide answers (partial or otherwise) - I would have thought that you would already know that. If you know that this is not the problem and you can point to the problem, then provide an answer yourself. – AJD Mar 29 '18 at 21:58