12 interval of Excel output
12 interval of Excel output
12 interval of Excel output
12 interval of Excel output
12 interval of Excel output
12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output
12 interval of Excel output 12 interval of Excel output
12 interval of Excel output
Go Back  Xtreme Visual Basic Talk > > > 12 interval of Excel output


Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2013, 04:51 AM
stahorse stahorse is offline
Newcomer
 
Join Date: Nov 2013
Posts: 20
Default 12 interval of Excel output


I have this code below with a loop, outputting 117 records into excel. I want to output 12 records then skip a line, another 12 then skip a line to the finish of all the data onto the same excel sheet. please help.

Code:
MySQL = "SELECT convert(datetime, RunMonth + '01', 112) as EffectiveMonth, SalaryBill, Rate " _
    & "From Hpacc4 " _
    & "where Scheme = '" & frmLogin.MaskEdBox1.Text & "' " _
    & "AND convert(datetime, RunMonth + '01', 112) <= '" + RunMonthDate + "01 ' " _
    & "AND convert(datetime, RunMonth + '01', 112) > DATEADD(yyyy,-5, convert(datetime, '20130201', 112)) " _
    & "AND AccCode = '110' order by EffectiveMonth "
        
        With rsHPData
            .CursorLocation = adUseClient
            .LockType = adLockReadOnly
            .Open MySQL, cnHPtest, adOpenForwardOnly, adLockReadOnly
        End With

        numRecs = rsHPData.RecordCount
         
         With oWS
           'SET THE TOP ROWS WITH TITLES--Change Font to Bold
            .Range("A2:C3").Font.Bold = True 'sets top row (stuff below) in bold print
              .Cells(2, 1).Value = "Month"
              .Cells(2, 2).Value = "Payroll"
              .Cells(2, 3).Value = "Rate"
         'Run through the RECORDSET, stating in ROW 2, until end of the RECORDSET
         For x = 3 To numRecs + 1
              .Cells(x, 1).Value = Format(rsHPData!EffectiveMonth, "mmmm-yyyy")
              .Cells(x, 2).Value = Trim(rsHPData!SalaryBill)
              .Cells(x, 3).Value = Trim(rsHPData!Rate)
              
         rsHPData.MoveNext  'Move through the RECORDSET

              'For
              ' I think twelve intervals has to come here
              'Next

         Next x

         End With
         'This for-loop makes the columns just wide enough for the largest 'string' in each column
         For x = 1 To 3 'where 3, in my case is three columns  (State Name, State Abbreviation and Date Entered Union
              oWS.Columns(x).AutoFit
           Next x
        'close down the rs and connection
        rsHPData.Close
        cnHPtest.Close
        oExcel.Visible = True  'so you can see what you did
        'set up the active excel sheet
        Set oWS = oExcel.ActiveSheet
        Set oWB = oExcel.ActiveWorkbook
        oWB.SaveAs FileName:=App.Path + "\Recon.xlsx"  'use whatever name you want here
    Screen.MousePointer = vbDefault

Last edited by stahorse; 12-05-2013 at 05:08 AM.
Reply With Quote
  #2  
Old 04-04-2014, 09:28 AM
JustinCase2 JustinCase2 is offline
Junior Contributor
 
Join Date: Apr 2006
Posts: 324
Default well

When you say "Skip a line", I assume you meen a line in the Excel sheet, not the recordset. So you want to insert the first 12 lines from the recordset into Excel rows 1-12, then put nothing in Excel row 13 then the Next 12 in rows 14-25. Am I Right? If so, do this:

Code:
dim iCount as integer = 0
For x = 3 To numRecs + 1
               iCount+=1
               If (iCount Mod 13) > 0 then ' If iCount can be divided by 13, this will be 0, and the line will be skipped
                   .Cells(x, 1).Value = Format(rsHPData!EffectiveMonth, "mmmm-yyyy")
                   .Cells(x, 2).Value = Trim(rsHPData!SalaryBill)
                   .Cells(x, 3).Value = Trim(rsHPData!Rate)   
                   rsHPData.MoveNext  'Move through the RECORDSET
              End If



         Next x
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
12 interval of Excel output
12 interval of Excel output
12 interval of Excel output 12 interval of Excel output
12 interval of Excel output
12 interval of Excel output
12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output 12 interval of Excel output
12 interval of Excel output
12 interval of Excel output
 
12 interval of Excel output
12 interval of Excel output
 
-->