Keskustelu: Koodit: Excel VBA moduuli esim. Juhannuksen vieton suunnittelua varten

'VBA Projektin referenssit:
'Microsoft ActiveX Data Objects 6.1 Library
'Microsoft XML, v6.0

'Module1
Sub auto_open()

    Dim basePath As String, savePath As String, orginal As String
    orginal = ThisWorkbook.Name

    basePath = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "")
    savePath = basePath & "alkon-hinnasto-tekstitiedostona.xlsx"
    Dim URL As String
    URL = "https://www.alko.fi/INTERSHOP/static/WFS/Alko-OnlineShop-Site/-/Alko-OnlineShop/fi_FI/Alkon%20Hinnasto%20Tekstitiedostona/alkon-hinnasto-tekstitiedostona.xlsx"
    Dim oHTTP As ServerXMLHTTP60
    Set oHTTP = New ServerXMLHTTP60
    oHTTP.Open "GET", URL, False
    oHTTP.setRequestHeader "Content-Type", "application/octet-stream"
    oHTTP.send
    Dim oStream As Stream
    Set oStream = New Stream
    oStream.Open
    oStream.Type = 1
    oStream.Write oHTTP.responseBody
    oStream.SaveToFile savePath, 2
    oStream.Close
    Set oStream = Nothing
    Set oHTTP = Nothing
    Workbooks.Open savePath

    For Each Workbook In Workbooks
       If Workbook.Name = "alkon-hinnasto-tekstitiedostona.xlsx" Then
            Workbook.Activate: Exit For
       End If
    Next

    For Each Workbook In Workbooks
       If Workbook.Name = orginal Then
            Workbook.Saved = True
            Workbook.Close: Exit For
       End If
    Next

End Sub