topic Re: Export Table Box in Batches in QlikView

Export Table Box in Batches

gerhardl — Thu, 23 May 2013 10:06:56 GMT

Hello,

I have a tricky one here (for me), that I need help with.

I have a macro that sends a table box to Excel and saves the excel file. The table box usually has between 200,000 and 300,000 records. This causes the macro to run very long and sometimes fail.

The file then also needs to be split - it is a list of customer detail which we upload to our SMS Portal to send them text messages, and we usually limit a batch to 50,000 at a time so that we do not get too many incoming calls at the same time because of the messages.

I want to amend the below macro to create separate files, each with 50,000 records, and the last file of course with how ever many records are left. If there are 210,000 I want the following Excel Files:

Insurance SMS " & strYear & strMonth & strDay &" Batch 1.xlsx" (50,000)

Insurance SMS " & strYear & strMonth & strDay &" Batch 2.xlsx" (50,000)

Insurance SMS " & strYear & strMonth & strDay &" Batch 3.xlsx" (50,000)

Insurance SMS " & strYear & strMonth & strDay &" Batch 4.xlsx" (50,000)

Insurance SMS " & strYear & strMonth & strDay &" Batch 5.xlsx" (10,000)

Is that possible? Here is my current code:

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

SET XLDoc = XLApp.Workbooks.Add

SET XLSheet1 = XLDoc.Worksheets(1)

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Qlikview\TEMP\Insurance SMS " & strYear & strMonth & strDay &".xlsx"

Set obj = ActiveDocument.GetSheetObject("TB01")

obj.CopyTableToClipboard True

XLSheet1.Paste XLSheet1.Range("A1")

XLApp.Worksheets(1).Range("A:B").ColumnWidth = 9.5

XLApp.Worksheets(1).Range("C:C").ColumnWidth = 3.5

XLApp.Worksheets(1).Range("D:E").ColumnWidth = 32

XLApp.Worksheets(1).Range("F:F").ColumnWidth = 5

XLApp.Worksheets(1).Range("G:G").ColumnWidth = 12

XLApp.Worksheets(1).Range("H:H").ColumnWidth = 7.5

XLApp.Worksheets(1).Range("I:I").ColumnWidth = 30

XLApp.Worksheets(1).Range("J:K").ColumnWidth = 13

XLApp.Worksheets(1).Range("G:G").NumberFormat = "#############"

XLApp.Worksheets(1).Range("I:I").NumberFormat = "#############"

XLApp.Worksheets(1).Cells.WrapText = False

XLApp.Visible = True

XLDoc.SaveAs NewFileName

XLApp.Quit

End Sub

NOTE: I am currently working on Personal Edition so cannot open any of your QV Files.

Thanks,

Re: Export Table Box in Batches

marcus_sommer — Thu, 23 May 2013 12:12:42 GMT

The simplest solution would be a calculated dimension in load, then you could use a simple excel-export in a loop through this field. Otherwise this should work with a little tuning on it:

.....

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Qlikview\TEMP\Insurance SMS " & strYear & strMonth & strDay

iRow = XLApp.Worksheets(1).Rows.Count

iFile = Application.RoundUp(iRow / 50000)

for i = 0 to iFile - 1

SET XLDocSplit = XLApp.Workbooks.Add

SET XLSheetSplit = XLDocSplit.Worksheets(1)

XLSheet1.Activate

XLSheet1.Range("A1:I1").Copy

XLSheetSplit.Activate

XLSheetSplit.Range("A1").Select

Selection.Paste

XLSheet1.Activate

XLSheet1.Range(Cells((i * 50000) + 2, 1), Cells(((i + 1) * 50000) + 2, 9)).Copy

XLSheetSplit.Range("A2").Select

Selection.Paste

XLDocSplit.SaveAs NewFileName & "-" & i & ".xlsx"

XLDocSplit.Close

....

- Marcus

Re: Export Table Box in Batches

gerhardl — Thu, 23 May 2013 13:27:24 GMT

Hi Marcus,

That seems a bit too difficult for someone with my QV knowledge, but I'll try that if I cannot get right what I am now attempting.

My idea was to add a row number to each record in the load script, and then bookmark different sets (e.g. row 1 - 50,000 is bookmark1). Then I activate a bookmark, export and save, then go to the next bookmark.

I am obviosuly doing something wrong though. The first export and save works fine, then I select a new bookmark, and then it fails - Paste method of Worksheet class failed. I'm trying to attach an image but it isn't working.

Re: Export Table Box in Batches

gerhardl — Thu, 23 May 2013 13:57:04 GMT

Okay so my "solution" is a nightmare.

Marcus, can you please explain your suggestion(s) to me?

I really don't know what your first paragraph means, and your suggested macro code - not sure how that would work and where I am selecting the QlikView object to export.

Re: Export Table Box in Batches

marcus_sommer — Thu, 23 May 2013 15:19:40 GMT

My suggest was after the full export to split the data in a loop and store each part in a new file. Your error message is probably only a small syntax-error.

But if you have a field with the "RowCluster" you can use it much easier for a selections-loop:

sub export

dim obj, field, i

dim NewFileName

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Qlikview\TEMP\Insurance SMS " & formatdatetime(Date, 2)

set field = ActiveDocument.Fields("RowCluster").GetPossibleValues

for i = 0 to field.count - 1

ActiveDocument.Fields("RowCluster").Select field.Items(i)

set obj = ActiveDocument.GetSheetObject("TB01")

obj.ExportBiff NewFileName & "-" & i & ".xls"

end sub

- Marcus

Re: Export Table Box in Batches

gerhardl — Thu, 23 May 2013 17:02:26 GMT

okay so I just had row numbers, but I have now grouped them into 9 different batches as part of my load script.

So the field name is Batch, and I want to select each of them in turn and create 9 different excel files.

I tried your code as is, and just replaced the fieldname "RowCluster" with "Batch", and I get the attached error message.

Re: Export Table Box in Batches

marcus_sommer — Fri, 24 May 2013 07:42:28 GMT

This code looks different to the code from me. Want you it solve without loop with repeat the same code several times? I don't believe this it the better way. A few trials will required with each solution ...

I' m not sure whether this paste method is properly. Most often it is easier (not performanter) to use:

XLSheet1.Activate

XLSheet1.Range("A1").Select

Selection.Paste

Try it with another object and much lesser data to see logic and syntax works.

- Marcus

Re: Export Table Box in Batches

gerhardl — Fri, 24 May 2013 08:26:54 GMT

Sorry, I loaded the wrong picture! See attached.

Re: Export Table Box in Batches

marcus_sommer — Fri, 24 May 2013 08:33:56 GMT

Yes it was not complete:

ActiveDocument.Fields("Batch").Select field.Items(i).Text

Re: Export Table Box in Batches

gerhardl — Fri, 24 May 2013 08:39:10 GMT

I still get the same error message - Object doesn't support this property or method: 'field.Items'

Re: Export Table Box in Batches

marcus_sommer — Fri, 24 May 2013 08:57:04 GMT

sorry Item instead Items:

ActiveDocument.Fields("Batch").Select field.Item(i).Text

Re: Export Table Box in Batches

gerhardl — Fri, 24 May 2013 09:12:20 GMT

Okay it executed now (took about 10 minutes), and I can see that it went through the different selections. Batch number 9 (the last one) was left selected after the macro finished.

But there are no excel files created.

Re: Export Table Box in Batches

marcus_sommer — Fri, 24 May 2013 09:21:44 GMT

Only selections could not take 10 minutes - check again the path.

To remove the last selection: ActiveDocument.Fields("Batch").Clear

Re: Export Table Box in Batches

gerhardl — Fri, 24 May 2013 09:48:20 GMT

Okay 10 minutes was not totally true. I timed it now and execution takes 2 minutes and 40 seconds.

Still nothing in the folder, so I removed the date from the field name amd changed how the date is chose. and now it works!

Below is the final code if someone else needs it. Thanks so much for all your help!!!

I have just one more question. In the Excel file, Column G is an account number with between 13 and 20 characters. Whenever I do a macro export the formatting doesn't remain text, it changes to something like

1.11001E+12

Can you amend the macro code to keep this in text format so the full account number shows in Excel, like this:

10201205040001512426

Current code that works (except for account number format)

sub export

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

dim obj, field, i

dim NewFileName

NewFileName = "C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Qlikview\TEMP\Insurance SMS " & strYear & strMonth & strDay

set field = ActiveDocument.Fields("Batch").GetPossibleValues

for i = 0 to field.count - 1

ActiveDocument.Fields("Batch").Select field.Item(i).Text

set obj = ActiveDocument.GetSheetObject("TB01")

obj.ExportBiff NewFileName & "- " & i & ".xls"

next

ActiveDocument.Fields("Batch").Clear

end sub