Excel.Workbook Writing with Reference to Cell (i)

[Snurry]

I'm trying to export a list of properties received from an API call out to an excel workbook. I'm quite inexperienced in the area but I'm trying to have the properties written relative to the cell (i) in the for loop.

The code is this at the moment:

Public Sub BHorse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BHorse.Click
        Dim oMarketsReq As New BFExchange.GetAllMarketsReq
        Dim oMarketsResp As BFExchange.GetAllMarketsResp
        With oMarketsReq
            .header = oHeaderAU()
            ReDim .eventTypeIds(0) : .eventTypeIds(0) = 7
            ReDim .countries(1) : .countries(0) = "AUS" : .countries(1) = "USA"
            .fromDate = Today
            .toDate = Today.AddDays(1)
        End With
        oMarketsResp = BetfairAU.getAllMarkets(oMarketsReq)
        With oMarketsResp
            CheckHeader(.header)
            Label6.Text = "ErrorCode = " & .errorCode.ToString
            If .errorCode = BFExchange.GetAllMarketsErrorEnum.OK Then
                Dim AllMarkets As New UnpackAllMarkets(.marketData)
                Dim oWB As Excel.Workbook = moApp.Workbooks.Add
                With AllMarkets
                    For i = 0 To .marketData.Length - 1
                        With .marketData(i)
                            Dim oSht As Excel.Worksheet = DirectCast(moApp.Workbooks.Add.Sheets("Sheet1"), Excel.Worksheet)
                            oSht.Cells(1, 1) = .marketId & " " & .marketStatus & "  " & .marketName & "  " & .menuPath
                            oSht.Cells(1, 2) = "the line is " & i
                            oSht.Cells(3, 1) = "2"
                            oSht.Cells(4, 1) = "4"
                            oSht.Cells(7, 1) = "=SUM(A3, A4)"
                            moApp.Visible = True
                            moApp.Workbooks.Add.Close(True, "C:\Data\market.xls")
                            oSht = Nothing
                            oWB = Nothing
                        End With
                    Next
                End With
            End If
        End With
    End Sub

This code runs with no errors but it creates a new workbook for each entry whereas I want all the data in the single workbook C:\Data\market.xls .

When I change

oSht.Cells(1, 1)

to

oSht.Cells(i, i)

I receive errors.

[emartinho]

This line

moApp.Workbooks.Add.Sheets

adds a new workbook. A simple example that adds a new sheet to the first open workbook before the active sheet of that workbook:

moApp.Workbooks(1).Sheets.Add

Hope this helps.
-EM