Visual Basic

Wayne Gaylard3-May-10 0:07

3-May-10 0:07

Hi Nishkarsh,

Carlos has a code generator on his site which will generate the code for any excel spreadsheet. This can be found here CarlosAg Code Generator[^]. If you design a sample spreadsheet, then you can use the code generator to generate the code for any .Net language. Most often you can use the code straight off, just ensuring the correct values go in the right places, otherwise a bit of refactoring is all that is needed.

Happy Coding

nishkarsh_k3-May-10 2:08

3-May-10 2:08

Hi Zimvbcoder,

It is great. Works really good. One of the easiest thing to output a Excel sheet.

The only other thing which i want to know is how to read from a Excel sheet.

Regards
Nishkarsh

nishkarsh_k4-May-10 3:27

4-May-10 3:27

Hi Zimvbcoder,

The lib is really great for making good looking XLS file output. I am having some issues and clarifications, can you please help me out.

1. Dose the lib provides any method to read from the excel sheet?

2. I read data from DB and used the codes given to create xls, is looks fine but when i went to save as option i realized it that the format is XML. Am i doing some thing wrong or it gives output in XML format only. Coz when i try to read the data from the generated file (in VB.net using OLEDB) i get file format error. And i Have to manually go and re-save the file in XLS format.

Any idea?

Regards
Nishkarsh Confused | :confused:

Wayne Gaylard4-May-10 20:44

4-May-10 20:44

Hi Nishkarsh

I am afraid the library is only for generating reports, and cannot read Excel spreadsheets. The only way I know of reading Excel spreadsheets without Excel installed on the machine is by using ADO.Net and connecting to the spreadsheet like a database. Unfortunately, I have never used this method and can't give any advice. As the file generated by CarlosAg library is in Spreadsheet XML format, what I do is if I want to read or add to the file, I treat it as a purely XML file and then do my business with XML.

As far as saving the file generated, the format of the generated file is Spreadsheet XML, but you can save this as an .xls file and Excel opens this file without an issue, just warning you that the file format is not correct the first time you open it. If you ignore that and open it, then you can save as any format you like from Excel itself. What I normally do is use a FileSaveDialog to get the user to select the name of the file and I use a filter to make sure it only selects .xls files like this :

VB.NET

Dim sfdReport As New SaveFileDialog
sfdReport.Filter = "Excel Files (*.xls)|*.xls"
sfdReport.ShowDialog()
book.save(sfdReport.FileName)

That way the file is only saved as .xls.

Happy Coding

nishkarsh_k5-May-10 0:28

5-May-10 0:28

Hi zimvbcoder,

Thanks for the input, i am doing the same as of now.

I will tel you what exactly i wanted to achieve. I have a application which has a long list of items and values (some thing like price list) which the client wants to update on his own from time to time. He is asking that give a provision so that the list is available in Excel he can update it and then the list can be updated in the system.

SO i am creating a application in which i am using CarlosAg to read from DB and create a excel file. Which client will update and then i will read it from the excel using OLEDB and update the DB (I know this is not the best way of updating a DB but client is finally the god) Blush | :O

Now CarlosAg gives output in XML format and even if i name it as .XLS then also OLEDB dose not read it. So i have to tel the client that please go to file menu and "Save As" .xls before he dose the step 2. This makes the solution not fully automated and chances are they mis out that step and we end up it issues... Dead | X|

Please do let me know if there is some thing else which we can do about it...

Regards
Nishkarsh

Wayne Gaylard5-May-10 1:41

5-May-10 1:41

Hi Nishkarsh.

Personally what I would do is to include a place in the app where the client can update the products, rather than saving to Excel and then reading from the spreadsheet. This way you cut out the Excel part altogether. If the customer really insists on a spreadsheet, then I would tell him that Excel needs to be installed on the machine, and use the Microsoft Interop Assemblies to read and write to Excel format. I agree with you that relying on the client to do anything in the right order is just going to cause headaches, and I do not know of a library that can read Excel files without a reference to the Excel dll. Sorry I can't be any more help.

nishkarsh_k6-May-10 19:53

6-May-10 19:53

Hi zimvbcoder,

I need a bit of help in reading .xml file. I was able to convince my client to have xml instead of xls as both can oven in excel interface. This will avoid 1 manual steep to of saving .xml as .xls. No i tried reading data from .xml files as i am new to it i am having some issues.

I am using XmlTextReader class for the same and looking for XmlNodeType.Text, it seams to be working but it dives me some data before and after the data life author and versions also. Which makes my work harder as i only want the files which displayed in the excel interface when i use it to open the .xml file.. Any idea? Unsure | :~

nishkarsh_k16-May-10 4:11

16-May-10 4:11

Hi zimvbcoder,

Thanks for the earlier help I had started using "CarlosAg Excel Xml Writer Library" it was a great experience. Now i am stuck at a point for last 2 days i am not able to move ahead at all.

- I have used a excel which had 2-3 merged cells as the report heading then the col headers followed by data lines. I am not able to add more then 6 lines (I am using a loop) a sample code is as followed

For j = 0 To 5
    Row = sheet.Table.Rows.Add
    'Row.AutoFitHeight = False
    For i = 0 To DataGridView1.Columns.Count - 1
       Row.Cells.Add("test", DataType.[String], "s100")
    Next
Next

The above code works fine

But the moment I increase "j" loop to 6 or more

For j = 0 To 6

the file is generated but when i open it using Excel 2007 it gives me error with worksheet settings and dose not opens it. I had tried all what i could have taught. Can you please help me with it.

Note : I am using "NON-STRONGLY NAMED" version as i did not knew the difference and it was recommended if user is not sure. Will that make any difference?

Regards
Nishkarsh

nishkarsh_k17-Aug-10 2:36

17-Aug-10 2:36

Hello every one...
I got to know what wrong i was doing(that time only) now just giving info as may be useful for others... There are variables for number of rows in the starting of the function. One needs to set them Poke tongue | ;-P

Naming Convention

programmervb.netc++30-Apr-10 0:58

programmervb.netc++

30-Apr-10 0:58

What naming convention do you use so that you can easily find your public functions/routines in intellisense. I love intellisense like my first born but they keep adding stuff to it and it is getting harder and harder to find what I am looking for. Of course we are always adding things to that make it harder to find things.

Humble Programmer

Eddy Vluggen30-Apr-10 1:12

Eddy Vluggen

30-Apr-10 1:12

The usage of namespaces can signigicantly reduces the number of items that are present in your intellilist. A naming-conventions' usage should be readability, not discoverability.

I are Troll Suspicious | :suss:

programmervb.netc++30-Apr-10 4:27

programmervb.netc++

30-Apr-10 4:27

Thank you that makes sense.
Another somewhat related question I see the big companies compile their own framework as dlls which makes them easy to handle when using them so the question is if I am sharing code across multiple projects but it is addedto almost constantly should I be compiling it as dll files and if so are their any performance benfit/loss in doing so. So what I am saying is will the code run faster as a dll than the code that is compiled in the executable also this code is shared by executables that will probably be ran concurrently I don't know if that makes a difference.
Thanks again.

Humble Programmer

Eddy Vluggen30-Apr-10 4:54

Eddy Vluggen

30-Apr-10 4:54

programmervb.netc++ wrote:
So what I am saying is will the code run faster as a dll than the code that is compiled in the executable also this code is shared by executables that will probably be ran concurrently I don't know if that makes a difference.

There's not much difference once the library is loaded. It might take a bit longer if you start to load libraries dynamically, especially if Windows' needs to search it's path. Static linking would mean that it loads the library at the start of the application (like it would be if it were part of the executable)

Libraries that are shared amongst applications would best be situated in the GAC[^].

I are Troll Suspicious | :suss:

DaveAuld30-Apr-10 1:20

DaveAuld

30-Apr-10 1:20

I am sure if you stick to the various guidelines you cannot go far wrong. Obviously your employer/or any project may have specific guidelines which you must adhere to, but i am sure they will soon tell you if they exist.

See the likes of here;
http://msdn.microsoft.com/en-us/library/ms229045.aspx[^]

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

Response.End not working in ActiveX DLL

fjdiewornncalwe30-Apr-10 3:22

fjdiewornncalwe

30-Apr-10 3:22

My answer would be about simplicity. Use namespacing to classify, and for the love of god name your methods to accurately reflect what they do and don't have the methods do any more.
i.e. DO NOT END UP WITH DoThisAndThatAndEverythingElseInHerePlease

neil.stevens29-Apr-10 22:22

neil.stevens

29-Apr-10 22:22

Hi,

I am putting together an ActiveX DLL for a web application that i am working on, the application is developed in Classic ASP and VB6.

In the DLL i am gaining access to the Intrinsic ASP objects using the Microsoft Active Server Pages Object Library and GetObjectContext().

The problem is that when i call MyResponse.End in the DLL, execution simply continues past

MyResponse.Write "

I am here!!

"
MyResponse.End
MyResponse.Write "

And I'm here too!!

" --> this line still executes in the DLL

Is there a reason for this, have i missed a setting on the webserver or is this an issue with using the Intrinsic ASP objects in a COM DLL environment.

Regards
Neil

Event implementation and autocomplete handler signature with Visual Studio 2008

hoernchenmeister29-Apr-10 20:30

hoernchenmeister

29-Apr-10 20:30

Good morning,
may somebody please be so kind and tell me how to autocomplete eventhandler signatures with VB?
I am used to the C# way like typing:
this.btnClickMe.Click += new EventHandler(handleTheEvent)

After the += I only need to tab the Tab key twice and get the handler implementation with the right signature.

In VB I can hover over the event and see the signature of the related handler, but I just do not know how to autocomplete this...

Is that possible? Not that I am lazy (of course not, who would have guessed that), I just want to save some time Wink | ;)

thanks in advance and ejoy your weekends,
best regards
Andy

Re: Event implementation and autocomplete handler signature with Visual Studio 2008

Dave Kreskowiak30-Apr-10 3:37

Dave Kreskowiak

30-Apr-10 3:37

There is no equivilent key combination in VB.NET to do this.

Are you kmaking a class exposing Custom Events? Or what...

I say this because you don't normally see the EventHandler class being used in VB.NET unless you're making Custom Events in controls.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Re: Event implementation and autocomplete handler signature with Visual Studio 2008

hoernchenmeister2-May-10 20:40

hoernchenmeister

2-May-10 20:40

The thing is that I develop in C#, creating custom classes/controls and stuff alike which (as you already guessed) expose a couple of events.
My colleague is developing in VB and there are times when I have to deal with the code written in VB.

I was just searching for a way to implement the custom events of the controls without having to check the documentation for the eventhandler signatures, which are sometimes quite complex.
Yeah, might be lazy Wink | ;)

I figured out that I can see the signature in the tooltip of the event, but when using "Handles" I just expected to get the signature automatically somehow...

thanks for the reply and have a great week,
cheers
Andy

Re: Event implementation and autocomplete handler signature with Visual Studio 2008

Dave Kreskowiak3-May-10 2:07

Dave Kreskowiak

3-May-10 2:07

If you're consuming a control, then you can get the header written for you by picking the control from the left drop down at the top of the code editor window, then the event you want in the right drop down. Other than that, I hate it too...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Re: Event implementation and autocomplete handler signature with Visual Studio 2008

hoernchenmeister3-May-10 2:36

hoernchenmeister

3-May-10 2:36

I mostly use classes, but there are some controls, so I'll go for your suggestion with the dropdowns.

Thanks a lot Dave, I really appreciate your helt

best regards
Andy

Splitting a text box with commas in it

offroaderdan29-Apr-10 8:30

offroaderdan

29-Apr-10 8:30

Experts,

I have a textbox which will generate a set of random numbers, e.g 10 , 4 , 6 , 3 , 2 , 1 ....

What i need to do is to basically split this textbox up into lots of textboxes. I was originally using e.g

txt1.text = mid(txt0.text, 1,txt0.textlength - 7)

this was ok if you where using numbers below 10 but now i am not, so i have split my numbers using commas but how do i sort them into the differnet text boxes using the commas as splitting points?

Many thanks

Dan

Re: Splitting a text box with commas in it

Luc Pattyn29-Apr-10 9:00

Luc Pattyn

29-Apr-10 9:00

string.Split() is the prime choice for splitting strings by a special character of your choice.

BTW: you would get into trouble if your numbers would exceed 999, your numbers include thousands separator, and your app were used by people using comma as a thousands separator (lots of West-European countries do this).

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.

Re: Splitting a text box with commas in it

Wayne Gaylard29-Apr-10 20:30