Excel, DB2, AS400 (retriving data and sending into Excell)

Hi,

I have to retrieve a data from AS400 DB2 and after working with data I have
to export into one of existing Excel file.

I can connect into specific library in AS400 DB2 using AS400 Client-Access
v5.2 program using (in VB.NET) ODBC driver (DSN Name …) . I can retrieve
datam work on it using VB.NET and I can send into 'NEW' Excel file.

My first problem starts here:
1- Every time I access in to DB2 I open the connection to retrieve the data
from specific table and close the connection.
2- Every connection I use SQL command such as SUM. AVG etc. Most of the time
(%90) retrieving the data from AS400 DB2 takes some time. (between 15second
to 55 second)
3- I have to do this operation at least 50 times at most.
4- I am using VB.NET to achieve this operation.

My first question is:
- I want to put some information on label which resides on main Form that
shows user about current activity. Such as (“Retrieving data for DEN60”….)
and so on.
I have 50 tables that I have to get their sum, avg, or something similar. So
I want some information that tells user what the program is doing.
Unfortunately I do not know how to achieve it?

Now my second problem is this:
1- I have a pre-define Excel file and has only one worksheet and its tab it
show AC2004 instead of Sheet1.
2- I have to send some data into this sheet (respectively into E11, G11 and
I11 cells)
3- After sending the data I have to close the Excel and warn the user that
data save into existing Excel file.
Again I do not know how to do this.

I search and study about Excel Object. So I can retrieve data from AS400 DB2
and send the data into any cell in new excel file but I am having difficulty
to achieve same operation when the end result must be existing excel file in
specific sheet and specific cell.

I thank you in advance that finding time to read my post and I hope you
might find time to help me.

Regards.
Niyazi

re: Excel, DB2, AS400 (retriving data and sending into Excell)

there are two ways to put data into Excel, that I know of -
- using automation (Excel object model)
- using ADO.NET - treating Excel as a database.

For the latter,
using the Jet OLEDB provider for Excel, you can refer to a named range like
this:

select * from [materials$]

eg, http://tinyurl.com/6yglu

or you can Create a sheet by running a CREATE TABLE command - the sheet is
the name you provide for the table.


for the former, there are lots of examples, I think you can find one in the
..NET SDK quickstart which is optionally installed when you install the .NET
SDK.

-D




"Niyazi" <Niyazi@discussions.microsoft.com> wrote in message
news:15F85DA6-0133-402A-BAB2-CC5F487E5F39@microsoft.com...[color=blue]
> Hi,
>
> I have to retrieve a data from AS400 DB2 and after working with data I
> have
> to export into one of existing Excel file.
>
> I can connect into specific library in AS400 DB2 using AS400 Client-Access
> v5.2 program using (in VB.NET) ODBC driver (DSN Name .) . I can retrieve
> datam work on it using VB.NET and I can send into 'NEW' Excel file.
>
> My first problem starts here:
> 1- Every time I access in to DB2 I open the connection to retrieve the
> data
> from specific table and close the connection.
> 2- Every connection I use SQL command such as SUM. AVG etc. Most of the
> time
> (%90) retrieving the data from AS400 DB2 takes some time. (between
> 15second
> to 55 second)
> 3- I have to do this operation at least 50 times at most.
> 4- I am using VB.NET to achieve this operation.
>
> My first question is:
> - I want to put some information on label which resides on main Form that
> shows user about current activity. Such as ("Retrieving data for DEN60"..)
> and so on.
> I have 50 tables that I have to get their sum, avg, or something similar.
> So
> I want some information that tells user what the program is doing.
> Unfortunately I do not know how to achieve it?
>
> Now my second problem is this:
> 1- I have a pre-define Excel file and has only one worksheet and its tab
> it
> show AC2004 instead of Sheet1.
> 2- I have to send some data into this sheet (respectively into E11, G11
> and
> I11 cells)
> 3- After sending the data I have to close the Excel and warn the user that
> data save into existing Excel file.
> Again I do not know how to do this.
>
> I search and study about Excel Object. So I can retrieve data from AS400
> DB2
> and send the data into any cell in new excel file but I am having
> difficulty
> to achieve same operation when the end result must be existing excel file
> in
> specific sheet and specific cell.
>
> I thank you in advance that finding time to read my post and I hope you
> might find time to help me.
>
> Regards.
> Niyazi[/color]

re: Excel, DB2, AS400 (retriving data and sending into Excell)

Hi Dino,

I already connected AS400 and I retrive the data and I can send into newly
created Excel sheet. My problem is how to send the data into exsiting Excel
sheet?

Here is my code
------------------------------------------------------------------------------------------------
'READ NAKIT DEGERLER and CALCULATE (1)
Dim Row1Num1, Row1Num2, Row1Num3 As Integer
Row1Num1 = Math.Abs(Math.Round(NakitDegerlerTP())
Row1Num2 = Math.Abs(Math.Round(NakitDegerlerYP())
Row1Num3 = Row1Num1 + Row1Num2


'Declare Excel object variables and create types
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)


'Insert data
xlSheet.Range("E11").Cells.Formula = FormatNumber(Row1Num1, 0,
TriState.False, TriState.False, TriState.UseDefault)
xlSheet.Range("G11").Cells.Formula = FormatNumber(Row1Num2, 0,
TriState.False, TriState.False, TriState.UseDefault)
xlSheet.Range("I11").Cells.Formula = FormatNumber(Row1Num3, 0,
TriState.False, TriState.False, TriState.UseDefault)

'Display the sheet
xlSheet.Application.Visible = True

'Save the sheet to C:\Test\1-BL100-A.XLS
xlSheet.SaveAs("C:\Test\1-BL100-A.XLS")


' Close Workbooks and Close the Excel Application.
xlApp.Workbooks.Close()
xlApp.Quit(
------------------------------------------------------------------------------------------------

I have a Excel document namerd as 1-BL100-A.XLS. Now I have to open this
excel sheet and send data into it. But I have to send into E11, G11 and I11
cell.

Yes I used Excel object model but when I try to send data into this existing
excel sheet it doesn't show other information but it goes and creates as a
new sheet.

I want to open this excel sheet and sent into the data, can you be kind
enough to help me. The link you provide didn't work. It is showing some C#
code but I am using VB.NET

Thank you.

Regards,
Niyazi