Excel Buffer Questions

Savatage · 2007-01-11

First off I was playing with Mark Brummels Excel Buffer Sample yesterday and it works great.
But I'm not too familiar with the excel buffer so I wanted to check it out to do some exporting of Sales Header info.

there is a line of code I added
EnterCell(Row, 2, "Ship-to Name", FALSE, FALSE, FALSE);

What exactly do each of the Falses do?

Also I have a Zip Code line
EnterCell(Row, 7, "Ship-to ZIP Code", FALSE, FALSE, FALSE);

But if the Zip code code starts with leading Zero's - the Zero don't get sent over - how do you force a "text format" to a field.

Thanks in advance!

David_Singleton · 2007-01-11

Savatage wrote:

First off I was playing with Mark Brummels Excel Buffer Sample yesterday and it works great.
But I'm not too familiar with the excel buffer so I wanted to check it out to do some exporting of Sales Header info.

there is a line of code I added
EnterCell(Row, 2, "Ship-to Name", FALSE, FALSE, FALSE);

What exactly do each of the Falses do?

Also I have a Zip Code line
EnterCell(Row, 7, "Ship-to ZIP Code", FALSE, FALSE, FALSE);

But if the Zip code code starts with leading Zero's - the Zero don't get sent over - how do you force a "text format" to a field.

Thanks in advance!

Var	Name	DataType	Subtype	Length
No	RowNo	Integer		
No	ColumnNo	Integer		
No	CellValue	Text		250
No	Bold	Boolean		
No	Italic	Boolean		
No	UnderLine	Boolean

Take a look at report 29 (Export Acc. Sched. to Excel) here find the function EnterCell. (You shoudl be able to see this code using report designer).

So to answer your question

Bold, Italic, Underline.

Savatage · 2007-01-11

yeah I just noticed it - thanks

Luc_VanDyck · 2007-01-11

Savatage wrote:

But if the Zip code code starts with leading Zero's - the Zero don't get sent over - how do you force a "text format" to a field.

And to answer the 2nd question: put a ' before your field so Excel treats it as text.

EnterCell(Row, 7, '''' + "Ship-to ZIP Code", FALSE, FALSE, FALSE);

Savatage · 2007-01-11

Yeah I figured that out too after trying all the other combinations ''' "'" """.

thanks I learned alot about the excel buffer in the last 2 days

couberpu · 2007-01-15

Good Morning All,

Stupid questions! :oops:

Where was report 29 used? and if I like to send account schedule to excel file, please show me how to do it!

Thanks,
CouberPu

Savatage · 2007-01-15

go to G/L->Account Schedules->Account Schedules(button)->Overview

this will run the account schedule you created or that was aready created

then you will see the function->Export Account schedule to Excel

Savatage · 2007-01-19

I've been real busy trying & testing excel buffer. But before I get to involved I was curious if you know if it is possible to add to a pre-existing file.

I am exporting sales header addresses to an excel file c:\addresses.xls
every day we add more sales orders and I want to export these new addresses into c:\addresses.xls starting from the last record that's already in the file.

Or does it just overwrite and create a new file?

Question2: I've read all there is to exporting pics to excel and I still don't get it.
I want to also export Item data - which works fine but I would also like the picture to export into excel also. The pics are all save externally already in a p:\ directory and the item # is the pictures name.

So what I would like to happen is when I open my Export Items To Excel report - I would select (for example) Starting Date=TODAY and all the new items entered today will go to excel - Item, Description, UPC, Price & Pic - All works but the pic part.

Thanks

Luc_VanDyck · 2007-01-19

Answer to Question 2: Automation: logo's in Excel

xlSheet.Shapes.AddPicture(FilePathAndName, 1, 1, 20, 20, 100, 100);

I have never tried it, but I know from a collegue that this works.

Savatage · 2007-01-19

Now do I add this to my report code - or do I alter the Excel Buffer with this code?

Luc_VanDyck · 2007-01-19

That code needs to be in the Excel Buffer table, as you need to have access to the Automation variable "XlWrkSht".

I don't have much experience with this Excel Buffer table, as I normally use an Excel Automation codeunit which contains the functions I need.

David_Singleton · 2007-01-19

Harry,

the problem is that (like a lot of stuff in Navision) they started out very strong, but just never went anywhere with it. So the excel buffer table has great features, and is great for getting you started, but always there is just "one more function" that you need.

Now unfortunately Excel Buffer is not one of the tables that we (MVPs through MSDN) have access to modify.

Normally for clients I have generally done the same as Luk, and maintained everything in a separate function instead of the built in functions in the Excel Buffer Table.

What is really needed is to move all the Excel Buffer functions from the table into a report, so that the normal report designer will allow "normal" users to add other properties.

Excel Buffer Questions

Comments

Categories