Progress bar Compile error

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi,

I have a progress bar and on clicking a button, it pops up and kicks off the below sub

It is giving me an error message

Compile Error:

Expected Variable or procedure, not module

It is pointing to line

Call OpenLatestFile

I never have any problems running subs calling multiple modules, what does it need?

I also attached the module it is having a problem with
Thanks
Seamus

---------




Sub IncrementCash()

Dim PctDone                 As Single
Dim x                       As Long

x = 1

Application.DisplayAlerts = False


Application.ScreenUpdating = False


Call OpenLatestFile
PctDone = x / 2
x = x + 1
Call UpdateProgressCash(PctDone)

Call Filter
PctDone = x / 2
x = x + 1
Call UpdateProgressCash(PctDone)

Unload UserForm1

   MsgBox "Report Complete"

End Sub

Sub OpenLatestFile()



Dim initPath As String, strFinalFile As String
Dim dteFile As Date, xlFile As String

   dteFile = Date
   ' Change Path of parent directory here
   ' Don't forget the "\" after the path
   initPath = "\\ukhibmdata02\rights\XLS\Tax\Global View Reporting\"

        xlFile = Dir(initPath & "\*.xls")
        Do Until xlFile = ""
            If dteFile = GetDateFromFileNameCashNostro(xlFile) Then
                strFinalFile = xlFile
                Exit Do
            End If
            xlFile = Dir
        Loop

         If Len(strFinalFile) > 0 Then
           Workbooks.Open initPath & strFinalFile
           Sheets("Detail").Visible = True
           Sheets("Detail").Select
         Else
          MsgBox "No new files"

         End If
End Sub



Function GetDateFromFileNameCashNostro(strFile As String) As Date
   ' returns date from last 8 characters of file name
   ' assumes ddmmyyyy format
   Dim dteTemp As Date, strTemp As String
   strTemp = Right$(Replace$(strFile, ".xls", "", , , vbTextCompare), 19)
   If Len(strTemp) < 19 Then Exit Function
    GetDateFromFileNameCashNostro = CDate(Mid(strTemp, InStr(strTemp, "20"), 10))
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Seamus - do you have a user form open when the macro is run? If so, Excel probably can't open a file if a form is open unless it modeless.

Author

Commented:
Yes i do! How can i make it modeless?
In the properties box, there is a "ShowModal" property - change it to False and see if that works.

Author

Commented:
It gave me the same error Stephen,

Thanks
Seamus
Hmm, ok not sure then. What if you just run OpenLatestFile directly without calling it?

Author

Commented:
Yep, then that sub runs fine and opens the file.

Its when i do it as part of the progress bar.....

Author

Commented:
I ran it straight from a sub and got the same error....strange?

----------

Sub RunReport()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Call OpenLatestFile
Call Filter

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
So it works when you run it, but not if you call it? Where are these macros - in the same module?

Author

Commented:
You just reminded me, i was calling the subs the same as the modules!

All sorted, thanks!

Seamus
Wow! Thanks for the points, don't feel I did much!

Author

Commented:
No probs, you made me seen the error and have given me plenty of help in the past!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial