[RESOLVED] Running 2003 workbook with Macro in 2007 - error; File Not Found VBA6.DLL

[NeedSomeAnswers]

Hi Everyone,

i am having a strange problem with Excel, I am looking after a some excel files for a colleague while they are away and i cant run the macro's in them.

My collegue has Excel 2003 and the file has been saved as a .xls, while i have 2007.

When i try and run the macro it error's saying - "File Not Found VBA6.DLL"

When i look this up it just seems to be the Visual Basic for Application reference. 2007 seems to have a slightly differently named file but it is there.

Also if i create a macro in a new workbook it works fine.

Does anyone have any ideas why i wouldn't be able to run a macro in a 2003 workbook inside 2007 ?

Thanks

[koolsid]

Show me the code that you are trying to run...

[NeedSomeAnswers]

No Problem, there is a class called clsSheetManager with the following code in it -

Private mSheet As String
Private mManager As String
 
Public Property Get Sheet() As String
    Sheet = mSheet
End Property
Public Property Let Sheet(ByVal v As String)
    mSheet = v
End Property
 
Public Property Get Manager() As String
    Manager = mManager
End Property
Public Property Let Manager(ByVal v As String)
    mManager = v
End Property

Then this is the main code;

Sub RePopulate()
    Dim cNames As Collection
    Dim oSN As clsSheetManager
    
    Dim shtAll As Object, rowAll As Long, colAll As Long
    Dim shtN As Object, rowN As Long, colN As Long
    Dim shtPrev As Object
    
    Dim sManager As String, sPrevManager As String
    
    Call Refresh_All
    Set shtAll = Sheets("All")
    
    Set cNames = New Collection
    rowAll = 2
    Do While Trim$(shtAll.Cells(rowAll, 2)) <> ""
        sManager = shtAll.Cells(rowAll, 2)
        If sManager <> sPrevManager Then
            Call AddNames(cNames, sManager, Left$(sManager, 1 + InStr(1, sManager, " ", vbTextCompare)))
        End If
        sPrevManager = sManager
        rowAll = rowAll + 1
    Loop
    
        
    shtAll.Columns("A:A").Select
    Selection.NumberFormat = "d-mmm-yyyy"
    
    Sheets("All").Select
    
    shtAll.Cells.Select
    shtAll.Cells.EntireColumn.AutoFit
    
    Range("A2").Select
    ActiveWindow.FreezePanes = True
 
    Set shtPrev = shtAll
    For Each oSN In cNames ' For each manager in the collection.
        rowN = 0
        colN = 0
        rowAll = 2
        On Error Resume Next
        Set shtN = Sheets(oSN.Sheet)
        If Err.Number = 0 Then 'found so delete and re-create
            Application.DisplayAlerts = False
            Call Sheets(oSN.Sheet).Delete
'            Call sht.Delete 'Sheets.Delete(oSN.Sheet)
            Application.DisplayAlerts = True
        End If
        On Error GoTo 0
        Set shtN = Sheets.Add(, shtPrev)
        Set shtPrev = shtN
        shtN.Name = oSN.Sheet
        
        colAll = 1
        Do While Trim$(shtAll.Cells(1, colAll)) <> ""
            colN = colAll
            shtN.Cells(1, colN) = shtAll.Cells(1, colAll)
            colAll = colAll + 1
        Loop
        
        rowAll = 2
        rowN = 2
        Do While Trim$(shtAll.Cells(rowAll, 2)) <> ""
            colAll = 1
            colN = 1
            If StrConv(Trim$(shtAll.Cells(rowAll, 2)), vbUpperCase) = _
               StrConv(Trim$(oSN.Manager), vbUpperCase) Then ' Manager name matches the require one.
                If StrConv(Trim$(shtAll.Cells(rowAll, 3)), vbUpperCase) <> _
                   StrConv(Trim$(shtAll.Cells(rowAll - 1, 3)), vbUpperCase) Then
                    rowN = rowN + 1
                End If
                Do While Trim$(shtAll.Cells(rowAll, colAll)) <> ""
                    colN = colAll
                    shtN.Cells(rowN, colN) = shtAll.Cells(rowAll, colAll)
                    colAll = colAll + 1
                Loop
                rowN = rowN + 1
            End If
            rowAll = rowAll + 1
        Loop
        
        shtN.Columns("A:A").Select
        Selection.NumberFormat = "d-mmm-yyyy"
    
        shtN.Columns("D:D").Select
        Selection.NumberFormat = "0.00"
        
        shtN.Rows("1:1").Select
        Selection.Font.Bold = True
        Selection.Font.Underline = xlUnderlineStyleSingle
 
        shtN.Cells.Select
        shtN.Cells.EntireColumn.AutoFit
    
        shtN.Select
        Range("A2").Select
        ActiveWindow.FreezePanes = True
    
    Next oSN
    
End Sub
 
Sub Refresh_All()
'
' Keyboard Shortcut: Ctrl+r
    
    For n = 3 To 13
        Sheets(n).Delete
    Next
    
    Sheets("All").Select
    Cells(1, 1).Select
    
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

[koolsid]

Ok click on Menu Tool~~>References

Uncheck the 1st reference to "Visual Basic For application" and then click on browse button and then navigate to this path...

"c:\Program Files\Common Files\Microsoft Shared\vba\vba6"

Click on vba6.dll

Also confirm that Microsoft office 12.0 object library is selected. If Microsoft office 11.0 object library is selected then unselect it...

Now give it a try...

[koolsid]

sorry that is vbe6.dll

[NeedSomeAnswers]

Right ok,

I dont have the VBA6.dll file. I have a file called VBE6.dll in the location. I am not sure why i don't have this file, should i with Office 2007 installed ?

Also i cannot untick the reference for "Visual Basic for Applications" as "the control or reference is in use".

The currently selected file is - C:\Windows\system32\MSVBVM60.DLL (for the Visual Basic for Applications reference)

The Microsoft office 12.0 object library is definitely selected

[NeedSomeAnswers]

Aha just seen your edit,

Tried this but firstly i still cannot remove the current reference to "Visual Basic for Applications", and secondly if i try to add it as well i get the error "Name conflict with existing module, project or object library"

I have noticed that if i open a brand new workbook the reference is set to the VBE6.DLL file.

[koolsid]

Do a file save as and saves it as .xlsm and then reopen the file and now check...

[RobDog888]

Two things going on...

1. You are interacting with a newer version
2. The newer version is possibly not macro enabled workbook.

So you need to answer, is the user going to have 2007 installed? If not then the 2007 workbook wont work.

[NeedSomeAnswers]

Hi Guys thanks for the help.

In answer to your question RobDog some will have 2007, some wont which is why the spreadsheet is in .xls format currently.

What i did to work around the problem is, firstly saved the workbook as a macro enabled workbook, then did my editing and finally then saved it back as a .xls file.

It seems that in 2007 if you have a .xls file with macro's in it they wont run once you have in anyway edited the macros in the file.

Koolsid :- i would add to your rep but apparently i need to "Spread it around" first :0)

[RobDog888]

Even with older versions, there still was the macro enabled prompt. With 2007 MS decided to separate macros from non-macroed workbooks.
This lead to the xlsm and basically all .???m file extensions in all 2007 Office file with the exception of Outlook.
If you were only dealing with 2003 and before versions Im sure this wouldnt have been a probfor you.

[scbailey]

FYI - I was having a related problem in Word VBA in Office 2007 where my Reference to Visual Basic for Applications was to C:\WINDOWS\system32\MSVBVM60.DLL but it couldn't resolve basic library functions like UserForm.Show vbModeless and thus couldn't compile. To resove I changed the following registry key:

HKEY_CLASSES_ROOT\TypeLib\{000204EF-0000-0000-C000-000000000046}\6.0\9\win32

From: C:\WINDOWS\system32\MSVBVM60.DLL
To: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL