Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

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

    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:\IDEA\IDEA 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:="\\UKFILE01\ABCDEF$\Globa l Statements\Global Extract Alldean - " & Format(Now, "DD-MM-YYYY") & ".XLS")

    On Error Resume Next

    ThisWorkbook.VBProject.VBComponents(MODULE_NAME).E xport 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

  2. #2
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I noticed it doesnt like to export the .bas file, for some reason it doesnt create it:


    Const TEMPFILE As String = "C:\IDEA\IDEA EXCEL MACROS.bas" 'temp textfile

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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you uncomment the On Error Resume Next line, what error do you get, if any?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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:\IDEA\IDEA 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:\IDEA\IDEA EXCEL MACROS.bas" 'temp textfile

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The file shouldn't be there after the code runs, since it deletes it after the import.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I think i have fixed this by adding the tick box to the developer macro settings in the trust centre - doh

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •