Export Table Box in Batches

[gerhardl]

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,

G

[marcus_sommer]

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

next

....

- Marcus

[gerhardl]

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.

[gerhardl]

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.

[marcus_sommer · Accepted solution]

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"

next

end sub

- Marcus

[gerhardl]

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.

[marcus_sommer]

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

[gerhardl]

Sorry, I loaded the wrong picture! See attached.

[marcus_sommer]

Yes it was not complete:

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

[gerhardl]

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