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

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

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.

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

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

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

Whats the error?

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

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 ":".

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

I changed the code to:

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

Then it says: "Compile error: expected ="

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

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

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

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

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

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.

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

Thats good! Glad you resolved it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.