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]

sorry Item instead Items:

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

[gerhardl]

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.

[marcus_sommer]

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

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

[gerhardl]

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