Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Excel automation export: Large Data Amount in one cell
StLi
Member Posts: 83
Hi!
I wrote an Excel Export with automations (Excelbuffer doesn't fit my needs) on a 4.0 SP3 Database
The Problem: i have one cell where i want to export the content of several records into one cell.
Because of the 1024 Char limit. I tried to do it like this (simplyfied):
For debugging i already filled the t1.text with the following data (one record per row)
... and i expected to the see something simular in my Excel Cell... unfortunatly i got this: (I'm really confused about this result.)
I already added a sleep(1000) command to the loop and what i saw looked like excel starts to overwrite and/or copy the last parts of the already entered data after the content reaches a certain size.... can anyone help me to get my expected result?
(actually i want to have line feeds between the records, but that's a different problem I'll address later.) The linefeed thing was easy: i just added a Char Variable set it to 10 and added it where i wanted to have it.
I wrote an Excel Export with automations (Excelbuffer doesn't fit my needs) on a 4.0 SP3 Database
The Problem: i have one cell where i want to export the content of several records into one cell.
Because of the 1024 Char limit. I tried to do it like this (simplyfied):
// xlWks is a Worksheet automation
// nextline is the current line I'm on
// N is just the column i happen to want to fill the data into
if t1.findset then
repeat
xlWks.Range('N' + FORMAT(nextLine)).Value(
FORMAT(xlWks.Range('N' + FORMAT(nextLine)).Value) + ' ' + t1.Text);
until t1.next = 0;
For debugging i already filled the t1.text with the following data (one record per row)
a1111111111111111111111111111111111111111111111111111111111111111111111111111111 b2222222222222222222222222222222222222222222222222222222222222222222222222222222 c3333333333333333333333333333333333333333333333333333333333333333333333333333333 d4444444444444444444444444444444444444444444444444444444444444444444444444444444 e5555555555555555555555555555555555555555555555555555555555555555555555555555555 f6666666666666666666666666666666666666666666666666666666666666666666666666666666 g7777777777777777777777777777777777777777777777777777777777777777777777777777777 h8888888888888888888888888888888888888888888888888888888888888888888888888888888 i9999999999999999999999999999999999999999999999999999999999999999999999999999999 j0000000000000000000000000000000000000000000000000000000000000000000000000000000 kAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA lBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB mCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
... and i expected to the see something simular in my Excel Cell... unfortunatly i got this: (I'm really confused about this result.)
a1111111111111111111111111111111111111111111111111111111111111111111111111111111 b2222222222222222222222222222222222222222222222222222222222222222222222222222222 c3333333333333333333333333333333333333333333333333333333333333333333333333333333 d44444444444 4mCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCmCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCm
I already added a sleep(1000) command to the loop and what i saw looked like excel starts to overwrite and/or copy the last parts of the already entered data after the content reaches a certain size.... can anyone help me to get my expected result?
(actually i want to have line feeds between the records, but that's a different problem I'll address later.) The linefeed thing was easy: i just added a Char Variable set it to 10 and added it where i wanted to have it.
0
Comments
To get you more detailed data, i started stepping the loop and here is what i got after each iteration: (i replaced the space with a pipe because i got multiple spaces next to each other at one point)
apparently the length stays the same after it reaches it's peak of 421. somehow after the 256th char, most of the content gets cut off. vor some reason the next char i add gets repeated 3 times and then comes one char from the previously cut of sequence (that 4) and then the newly added string get added... two times ... plus the first char from the 3rd time.
edit: sorry for messing up the forum layout.
I've discovered this Automation: 'Navision Attain Hash 1.0'.BSTRConverter
Now, I write the Data i want to export into the BStr-automation and i can assign it to the cell using
xlWks.Range('N' + FORMAT(nextLine)).Value := BStrConv.BSTR;however i get an overflow error if the lenght of BStrConv exceeds 1024 chars. I've currently solved it by filling the portions into an array and assigning the content to different Cells but I'm still not happy with that solution. In case someone has solved this issue, i'd be glad to hear about it... P.s. I can't send the whole automation to the value (like they did it on the same issue wird the mail body on other threads in this forum.) i have to use the BSTR-Property.
best regards