VBA code to automate logbook data import into excel

[Rfarkash]

Sorry if this is the wrong forum - please move if needed.
Trying to figure out how to write a vba macro to automatically import logbook data into an excel spreadsheet for analysis. I requested and received a Logbook API key, but so far haven't been able to successfully import.
Full disclosure - know my way around excel and VBA, not a computer guy otherwise and API/JSON/GITHUB etc are just a bunch of letters to me right now lol.
Anybody have a simple macro to automate the import they would be willing to share, or pointers on where to start from scratch?
Thx
Ron

[James_smith]

Here’s a VBA script to automate the import of logbook data into Excel from a CSV or text file. This script allows you to browse and select a file, then imports its content into a worksheet.

Steps to Use the VBA Code:
Open Excel and press ALT + F11 to open the VBA Editor.
Click Insert > Module.
Copy and paste the following VBA code.
Run the macro named ImportLogbookData().
VBA Code:
vba
Copy

Edit

Code: Select all

Sub ImportLogbookData()
    Dim ws As Worksheet
    Dim filePath As String
    Dim fileDialog As FileDialog
    Dim lastRow As Long
    
    ' Set the worksheet where data will be imported
    Set ws = ThisWorkbook.Sheets("LogbookData") ' Change to your sheet name
    
    ' Open File Dialog to Select Logbook File
    Set fileDialog = Application.FileDialog(msoFileDialogFilePicker)
    fileDialog.Title = "Select Logbook File"
    fileDialog.Filters.Clear
    fileDialog.Filters.Add "CSV Files", "*.csv;*.txt", 1
    
    If fileDialog.Show = -1 Then
        filePath = fileDialog.SelectedItems(1)
    Else
        MsgBox "No file selected. Import cancelled.", vbExclamation
        Exit Sub
    End If
    
    ' Clear old data before import
    ws.Cells.Clear
    
    ' Import data using QueryTables
    With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFilePlatform = xlWindows
        .Refresh
    End With
    
    MsgBox "Logbook data imported successfully!", vbInformation
End Sub

How This Works:
Opens a file dialog to select the logbook file.
Clears previous data before importing new data.
Imports CSV or text-based logbook data into Sheet "LogbookData".
Displays a message once the import is complete.

Let me know if you need modifications like date formatting, column selection, or specific delimiters!

[louiseravot]

Start by using VBA’s XMLHTTP to call the API, then use a JSON parser like VBA-JSON to handle the data and import it into Excel. Check online for simple VBA JSON tutorials to help you get started.

[Citroen]

If you're going to post code on here please use [code][/code] tags. They make your code more readable and easier to copy.

There's a [</>] icon when you create a posting that adds them automatically.

I've edited your posting, using my moderator powers.

[louiseravot]

Start by using VBA’s XMLHTTP to call the API, then use a JSON parser like VBA-JSON to handle the data and import it into Excel. Check online for simple VBA JSON tutorials to help you get started.

Start by using VBA’s XMLHTTP to call the API, then use a JSON parser like VBA-JSON to handle the data and import it into Excel. Check online for simple VBA JSON tutorials to help you get started. For location-based API access or delivery info, you can also explore this postcode tool.