.Bas file worked in XP but not in Windows 7 VBA Copy

Topic

New Reply

Hi,

I had the below macro which worked fine on my windows xp machine, I have now changed to windows 7 and it doesn’t appear to create the .bas file.

I have exactly the same directories etc, any ideas?

Thanks

Sub STATEMENTS_COPY()

Const MODULE_NAME As String = “STATEMENTS_MODULE” ‘name of module to transfer
Const TEMPFILE As String = “C:IDEAIDEA EXCEL MACROS.bas” ‘temp textfile
Dim wb As Workbook
Dim btn As Button

Name = “Global Extract Alldean – ” & Format(Now, “DD-MM-YYYY”) & “.XLS”

Set wb = Workbooks.Open(Filename:=”\UKFILE01ABCDEF$Global StatementsGlobal Extract Alldean – ” & Format(Now, “DD-MM-YYYY”) & “.XLS”)

On Error Resume Next

ThisWorkbook.VBProject.VBComponents(MODULE_NAME).Export TEMPFILE
wb.VBProject.VBComponents.Import TEMPFILE

Kill TEMPFILE

With wb
.Sheets.Add.Name = “Click For Statement”
Set btn = .ActiveSheet.Buttons.Add(34.5, 24, 666, 203.25)
End With
With btn
.OnAction = “‘” & wb.Name & “‘!STATEMENTS”
.Caption = “Click To Generate Statement”
With .Font
.Name = “Arial”
.FontStyle = “Regular”
.Size = 10
End With
End With
wb.Save

Excel.Application.Quit

End Sub

Reply | Quote

Replies

I noticed it doesnt like to export the .bas file, for some reason it doesnt create it:

Const TEMPFILE As String = “C:IDEAIDEA EXCEL MACROS.bas” ‘temp textfile

ThisWorkbook.VBProject.VBComponents(MODULE_NAME).E xport TEMPFILE

Reply | Quote

Hi Rory,

I dont have the comment in my version think it may just be on here.

The “STATEMENTS_MODULE” exists in the open workbook
And the diredtory of C:IDEA exists
and the file name open is IDEA EXCEL MARCOS so i’m adding the .bas to it.

when on windows XP this works fine, i can even search for the “C:IDEAIDEA EXCEL MACROS.bas and it says what file do you which to open this with:

In Windows 7 the file just isnt there

Const MODULE_NAME As String = “STATEMENTS_MODULE” ‘name of module to transfer
Const TEMPFILE As String = “C:IDEAIDEA EXCEL MACROS.bas” ‘temp textfile

Reply | Quote

The file shouldn’t be there after the code runs, since it deletes it after the import.

Reply | Quote

If you uncomment the On Error Resume Next line, what error do you get, if any?

Reply | Quote

I think i have fixed this by adding the tick box to the developer macro settings in the trust centre – doh

Reply | Quote

If you mean the ‘Trust access to the VBA Project’ option, you should have had an error if that wasn’t checked, since you said that you didn’t have an On Error Resume Next line in the real code.

Reply | Quote