VBA Open URL's in Excel 2013, 64bit

Question

I'm trying to open links via vba but I'm having lots of problems, here is what I was using previously:

Sub OpenUrl()

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With

    Dim lSuccess As Long
    Dim LastRow as Long

    LastRow = Range("A65536").End(xlUp).Row

    For Cell = LastRow To 1 Step -1
        'lSuccess = ShellExecute(0, "Open", Range("D" & Cell).Value)
        ThisWorkbook.FollowHyperlink (Range("D" & Cell).Value)
    Next Cell

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With

End Sub

However when it gets to the ThisWorkbook.Followhyperlink part it throws a "Out of memory" even though my system has 64GB of memory.

Trying the shell execute route from: http://support.microsoft.com/kb/224816 doesn't work either for me as it returns:

Anyone have any ideas?

https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx, http://stackoverflow.com/q/26822004/11683 — GSerg, Feb 12 '15 at 21:59
I'm using excel 2013 on a 64bit machine as well and I'm was able to copy/paste your sub and follow 15 hyperlinks successfullly. — jamesC, Feb 12 '15 at 22:20
@jamesC - there's a difference between having a 64-bit OS and running the 64-bit version of Office. Most people (I think) still run the 32-bit Office, even if they have a 64-bit OS like Win7. If you need Office to work with older add-ins etc, then that's a safer bet. — Tim Williams, Feb 12 '15 at 22:32
@TimWilliams correct I am also running 64 bit office (at work) so can't test now from home. — jamesC, Feb 12 '15 at 22:56
@TimWilliams I completelyforgot I have an autoInstallVBA where I have #If Win64 And VBA7 Then Private Declare PtrSafe Function GetCurrentDirectory_ _ Lib "kernel32" Alias "GetCurrentDirectoryA" _ (ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Private Declare PtrSafe Function SetCurrentDirectory_ _ Lib "kernel32" Alias "SetCurrentDirectoryA" _ (ByVal lpszCurDir As String) As Long // should have figured out why it was working for me :/ ha — jamesC, Feb 13 '15 at 13:58

SierraOscar · Accepted Answer · 2015-02-13T09:11:37.897

If you're using an API in 64-bit office then you may need to declare it as 'pointer safe' and use the VBA7 LongPtr type. You can use conditional compilation to test the environment using the VBA7 Win64 constant:

#If Win64 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"( _
    ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As LongPtr) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA"( _
    ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Have look at this MSDN article about compatibility between 32-bit and 64-bit Office with VBA for more information

VBA Open URL's in Excel 2013, 64bit

1 Answers1