topic Re: Insert blank row after each 3 record in QlikView

Insert blank row after each 3 record

prabir_c — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I want to insert 3 blank row after each Data in a Excel. following is my data-

I want the data as below format.

This means I have to Copy the full data and paste it in a excel. Then Go to last data record and then using a for loop insert a blank row upto A2. Because A1 is the heading.

Please Help. !

Re: Insert blank row after each 3 record

m_woolf — Fri, 17 Jun 2016 14:07:53 GMT

See the attached qvw

Re: Insert blank row after each 3 record

tamilarasu — Fri, 17 Jun 2016 15:34:59 GMT

Hi,

I am using personal edition. So, I am not sure what you have tried. I prepared a sample file from your screenshot and attached the same.

Sub Test()
Dim XLApp, XLDoc, XLSheet

FileName = "Test.xlsx"
FilePath = ActiveDocument.GetVariable("vVar").GetContent.String

If Right(FilePath,1)<> "\" then
FilePath = FilePath & "\"
End If

File = FilePath & FileName

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False 'True to display the excel
Set XLDoc = XLApp.Workbooks.Add
Set XLSheet= XLDoc.Worksheets("Sheet1")

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard True
XLSheet.Paste

LastRow = XLSheet.Cells(XLSheet.Rows.Count, 1).End(-4162).Row

For i = LastRow To 3 Step -1
XLSheet.Rows(i).Resize(3).InserT
Next

XLSheet.Range("A1").Select
XLDoc.SaveAs File
XLApp.Application.quit

Set XLApp = Nothing
Set XLDoc = Nothing
Set XLSheet = Nothing
Msgbox "Exported Sucessfully" & VbNewline & vbNewline & "File path: " & File, vbInformation , "Export"
End Sub

If you have any issues, let me know.

Re: Insert blank row after each 3 record

prabir_c — Mon, 20 Jun 2016 04:22:17 GMT

Thank You Tamil for your help.. It is working perfectly.

Re: Insert blank row after each 3 record

prabir_c — Mon, 20 Jun 2016 04:22:58 GMT

Thanks m w fro your help.

Re: Insert blank row after each 3 record

tamilarasu — Mon, 20 Jun 2016 06:46:31 GMT

Prabir,

Great. I just opened your file and modified few lines of code. Attached the modified file. Hope it helps someone someday.

Re: Insert blank row after each 3 record

Wed, 05 Oct 2016 07:15:30 GMT

Hi Tamil,

How do you enter a blank row every time the text "Total" is found in the same column?

Re: Insert blank row after each 3 record

tamilarasu — Thu, 06 Oct 2016 02:16:44 GMT

Hi Clare,

Please find the attached file. Let me know If you have any questions.

Re: Insert blank row after each 3 record

Anil_Babu_Samineni — Thu, 06 Oct 2016 05:04:52 GMT

Tamil, I don't know what i did wrong here, would you suggest me here. Default when i am trying to hit the Button it goes to Code.

Sub ExcelFile

Dim XLApp, XLDoc, XLSheet

FileName = "Z:\Test.xlsx"

FilePath = ActiveDocument.GetVariable("vVar").GetContent.String

If Right(FilePath,1)<> "\" then

FilePath = FilePath & "\"

End If

File = FilePath & FileName

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False 'True to display the excel

Set XLDoc = XLApp.Workbooks.Add

Set XLSheet= XLDoc.Worksheets("Sheet1")

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard True

XLSheet.Paste

LastRow = XLSheet.Cells(XLSheet.Rows.Count, 1).End(-4162).Row

For i = LastRow To 3 Step -1

XLSheet.Rows(i).Resize(3).InserT

XLSheet.Range("A1").Select

XLDoc.SaveAs File

XLApp.Application.quit

Set XLApp = Nothing

Set XLDoc = Nothing

Set XLSheet = Nothing

Msgbox "Exported Sucessfully" & VbNewline & vbNewline & "File path: " & File, vbInformation , "Export"

End Sub

Re: Insert blank row after each 3 record

tamilarasu — Thu, 06 Oct 2016 06:12:28 GMT

Hi Anil,

You have to change the macro security setting like in the below picture.

Also I did a minor change in your code.

Sub ExcelFile

Dim XLApp, XLDoc, XLSheet

File = "Z:\Test.xlsx"

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False 'True to display the excel

Set XLDoc = XLApp.Workbooks.Add

Set XLSheet= XLDoc.Worksheets("Sheet1")

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard True

XLSheet.Paste

LastRow = XLSheet.Cells(XLSheet.Rows.Count, 1).End(-4162).Row

For i = LastRow To 3 Step -1

XLSheet.Rows(i).Resize(3).InserT

XLSheet.Range("A1").Select

XLDoc.SaveAs File

XLApp.Application.quit

Set XLApp = Nothing

Set XLDoc = Nothing

Set XLSheet = Nothing

Msgbox "Exported Sucessfully" & VbNewline & vbNewline & "File path: " & File, vbInformation , "Export"

End Sub

Re: Insert blank row after each 3 record

Thu, 06 Oct 2016 06:21:24 GMT

Hi Tamil,

Thank you, I am very new to this so I might be doing something wrong or what I am asking for is incorrect.

I have attached what I would like to achieve in excel.

Re: Insert blank row after each 3 record

tamilarasu — Fri, 07 Oct 2016 06:22:00 GMT

No problem Clare. Is it possible to attach your application or sample one.? It would be easy for me to help you better. I would suggest you to start a new question.