none
Exel 2010 and VBA - create Excel - need help on copy of headers RRS feed

  • Question

  • Hi,

    For the past week I have been working on a project for my boss to create a front end to a couple queries.  Using a button in an Excel spreadsheet and a User Form in VBA - I get a parameter from a range of Excel columns.  I then use that parameter to run q SQL queries - all of this is working.

    The end results are copied to a new Excel workbook - I am trying to get the results side by side and am able to get all of the data correct except for the headers on the second query.  The headers for the second query always start at A1 - which has the headers for the first query.

    Set Rst = cnn1.Execute(SqlStatement) Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = True .Workbooks.Add .Sheets("Sheet1").Select 'Step 5: Copy the recordset to Excel .ActiveSheet.Range("A2").CopyFromRecordset Rst 'Step 6: Add column heading names to the spreadsheet For i = 1 To Rst.Fields.count xlApp.ActiveSheet.Cells(1, i).Value = Rst.Fields(i - 1).Name Next i xlApp.Cells.EntireColumn.AutoFit End With Set Rst1 = cnn1.Execute(SqlStatement1) 'Step 5: Copy the recordset to Excel xlApp.ActiveSheet.Range("E2").CopyFromRecordset Rst1 'Step 6: Add column heading names to the spreadsheet For b = 1 To Rst1.Fields.count xlApp.ActiveSheet.Cells(1, b).Value = Rst1.Fields(b - 1).Name Next b xlApp.Cells.EntireColumn.AutoFit

    The bolded area is the part that is creating the header records for the second query. I have yet to figure out how to get the header records to start at E1 since the results start at E2.

    I am not a programmer so I am doing this piecemeal for each part I need to do.  The code I used for the results were the only ones I could really get to work and remotely understand.

    Thanks for any help,

    Brian

    Monday, June 29, 2015 1:15 PM

Answers

  • Hello Cylon,

    The 'cells' object has two parameters: Line and column.

    Your code references line 1 and column references the variable b, that stars with the value 1.

    So, line 1 and column 1 = Range A1

    Use another variable to control the columns.

                      

    j = 5

    For b = 1 To Rst1.Fields.count xlApp.ActiveSheet.Cells(1, j).Value = Rst1.Fields(b - 1).Name

    j = j + 1 Next b


    • Marked as answer by CylonRed Monday, June 29, 2015 1:54 PM
    Monday, June 29, 2015 1:31 PM

All replies

  • Hello Cylon,

    The 'cells' object has two parameters: Line and column.

    Your code references line 1 and column references the variable b, that stars with the value 1.

    So, line 1 and column 1 = Range A1

    Use another variable to control the columns.

                      

    j = 5

    For b = 1 To Rst1.Fields.count xlApp.ActiveSheet.Cells(1, j).Value = Rst1.Fields(b - 1).Name

    j = j + 1 Next b


    • Marked as answer by CylonRed Monday, June 29, 2015 1:54 PM
    Monday, June 29, 2015 1:31 PM
  • I knew it had to be pretty simple and I figured a new variable was needed - just did not fully understand the how the references to cells worked.

    This worked and I appreciate the help.

    Monday, June 29, 2015 1:54 PM