Compile Error

[PC User]

I'm getting a compile error on my spreadsheet that works on a Windows 32bit system, but not on my upgraded Windows 64 bit system. I'm not familiar with what's needed to correct this. Could someone please help me? The error occurs for a Private Function:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

This is the full code for the spreadsheet module.

Option Explicit


Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long


Sub GetData()
    Dim ParameterSheet As Worksheet
    Dim DataSheet As Worksheet
    Dim ticker As String
    Dim exchange As String
    Dim interval As Integer
    Dim numPastTradingDays As Integer
    Dim qurl As String
    Dim CSVstatus As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual


    Set ParameterSheet = Sheets("Parameters")
    Set DataSheet = Sheets("Data")


    DataSheet.Cells.Clear
    ticker = ParameterSheet.Range("ticker").Value
    exchange = ParameterSheet.Range("exchange").Value
    interval = ParameterSheet.Range("interval").Value
    numPastTradingDays = ParameterSheet.Range("numTradingDays").Value


    qurl = "http://www.google.com/finance/getprices?" & _
           "q=" & ticker & _
           "&i=" & interval & _
           "&p=" & numPastTradingDays & "d" & _
           "&f=d,o,h,l,c,v"


    CSVstatus = URLDownloadToFile(0, qurl, "C:\quotes.csv", 0, 0)


QueryQuote:
    With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("a1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With


    DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
                                                      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                      Semicolon:=False, Comma:=True, Space:=False, other:=False


    DataSheet.Columns("A:G").ColumnWidth = 12


    '===Convert Google timestamp to Excel timestamp (only for Windows)
    Dim timeStamp As Double
    Dim timeStampRaw As String
    Dim timeZoneOffsetRaw As String
    Dim timeZoneOffset As Variant
    Dim numRows As Integer
    Dim i As Integer
    numRows = DataSheet.UsedRange.Rows.Count - 1


    timeZoneOffsetRaw = DataSheet.Range("a7")
    timeZoneOffset = (Mid(timeZoneOffsetRaw, InStr(timeZoneOffsetRaw, "=") + 1, 10))


    For i = 8 To numRows


        If Not IsNumeric(DataSheet.Range("a" & i)) Then


            timeStampRaw = DataSheet.Range("a" & i)
            timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) - 1))
            timeStamp = (timeStamp + timeZoneOffset * 60)
            DataSheet.Range("g" & i) = timeStamp / 86400 + 25569


        Else


            DataSheet.Range("g" & i).FormulaR1C1 = "=(RC[-6]*" & interval & "+" & timeStamp & ")/86400+25569"


        End If


    Next


    DataSheet.Range("g8:g" & numRows).NumberFormat = "d mmm yyyy h:mm;@"
    DataSheet.Range("G:G").Columns.AutoFit


    Application.Calculation = xlCalculationAutomatic


End Sub

 

[mole999]

is it 64 bit excel now?

 

[Teeroy]

Try changing:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

to:

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _"URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long

I haven't tried this (as I don't have your data structure) so please try on a COPY of your workbook first.

A good reference is Compatibility Between the 32-bit and 64-bit Versions of Office 2010.

 

[PC User]

Thanks very much. I'm using Excel 2016. I get no errors on your code corrections, however, it doesn't download the data to the harddrive. I've place the file into a temporary 2 day file storage website for you to look at.
ExpireBox | Google IEOD Stock Quotes.xlsm

QUOTE=Teeroy;4897009]Try changing:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

to:

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _"URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long

I haven't tried this (as I don't have your data structure) so please try on a COPY of your workbook first.

A good reference is Compatibility Between the 32-bit and 64-bit Versions of Office 2010.[/QUOTE]