VB6 - How to write a blank Line after writing each record.

[tgifgemini]

Hi Members,
please can anyone show me how I can write a blank line after writing each records? My intension is to space out the data in the spreadsheet for readability. So far the code I'm using is not doing it.

Seem my module below:

i = 5
 Do While Not rsin.EOF
    i = i + 1
    xlWksht.Cells(i, 1).Value = rsin![RequestID]
    xlWksht.Cells(i, 2).Value = rsin![Priority]
    xlWksht.Cells(i, 3).Value = rsin![Initials]
    xlWksht.Cells(i, 4).Value = rsin![Status]
    xlWksht.Cells(i, 5).Value = rsin![DReceivedDate]
    xlWksht.Cells(i, 6).Value = rsin![UpdateDate]
    xlWksht.Cells(i, 7).Value = rsin![Description]
    xlWksht.Cells(i + 1, 1).Value = Chr(10)  'Add a blank line after each record.
    rsin.MoveNext
 Loop

Thanks.
tgif

[binoj_daniel]

Use this
xlWksht.Range(i + 1:1).Insert(Shift:=xlDown)

instead of xlWksht.Cells(i + 1, 1).Value = Chr(10) 'Add a blank line after each record.

Let me know.

[tgifgemini]

Hi Binoj,
my module is not accepting the syntax: "xlWksht.Range(i + 1:1).Insert(Shift:=xlDown)". Hence, it's not working.
Thanks.
tgif

[binoj_daniel]

Whats the error?

[tgifgemini]

when I type in your code and try to compile, it beeps and hilights it in red.

It says: "Compile error". Expected: List separator or )", then it hilights the semicolon ":".

[tgifgemini]

I changed the code to:

xlWksht.Range(i + 1, 1).Insert(Shift:=xlDown)

Then it says: "Compile error: expected ="

[binoj_daniel]

Which version of excel are you using? And are you on vb6 or vb.net?

[binoj_daniel]

Here is another way of doing it. After you populate your excel file with all the records. Run this function.

Public Sub InsertRows()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Row As Long

Set xl = New Excel.Application
Set wb = xl.Workbooks.Open("C:\test.xls")
Set ws = wb.Sheets("Sheet1")
ws.Select
With ActiveSheet
For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row Step -1
.Rows(Row).Offset(1).Insert
Next Row
End With
End Sub

[tgifgemini]

Hi Binoj,
thank you for your insight and help. I achieved the objective by simply increasing the row variable by either 1 or two to skip a space or two before writing another line:

i = 6
xlwksht.cells(i + 2, 1).Value = ""

Thanks again.
tgifgemini.

[binoj_daniel]

Thats good! Glad you resolved it.