Mr. Luckyco thanks for the respond. And about the last question i asked on the printer display issue in which i send a demo project in your email. But is like you did not have time to go through my demo project, because i have been expecting to see your reply in my email but i have not seen it yet, even though i have seen your respond on the forum on which you suggested that i should use window. but because i did not understand you or may be i dont know how to relate the program with window that's why i didnt respond back. Another way i am trying to go about it, is by exporting the report (crystal report) to word or excel. But my problem is, i dont know how to generate the code. So pls can you help me out, because i saw some of your previous post where you generate code for exporting msflexgrid to excel.

madas:

Mr. Luckyco thanks for the respond. And about the last question i asked on the printer display issue in which i send a demo project in your email. But is like you did not have time to go through my demo project, because i have been expecting to see your reply in my email but i have not seen it yet, even though i have seen your respond on the forum on which you suggested that i should use window. but because i did not understand you or may be i dont know how to relate the program with window that's why i didnt respond back. Another way i am trying to go about it, is by exporting the report (crystal report) to word or excel. But my problem is, i dont know how to generate the code. So pls can you help me out, because i saw some of your previous post where you generate code for exporting msflexgrid to excel.

Sir
am not at your end to enable me get exactly what you want me do. Yes u sent a demo for me but I have given u solution, even sent you code that will enable you send document to your printer(s) since you dont want to use printer dialogue box. But if you think the solution is not enough then we can start afresh.


U dont need to write any code for export in crystal report. Enable the icon for that and u are done.

If you have crystal report in your system, use vb to add it and it will create a form for you embedding crystal report control viewer, get its property and play with it as u are changing the property keep running until u know their functions that will enable you export your report to any format u like.

luckyCO:

Sir
am not at your end to enable me get exactly what you want me do. Yes u sent a demo for me but I have given u solution, even sent you code that will enable you send document to your printer(s) since you dont want to use printer dialogue box. But if you think the solution is not enough then we can start afresh.


U dont need to write any code for export in crystal report. Enable the icon for that and u are done.

If you have crystal report in your system, use vb to add it and it will create a form for you embedding crystal report control viewer, get its property and play with it as u are changing the property keep running until u know their functions that will enable you export your report to any format u like.

Thank you very much for the respond. I will play arround with the crystal report properties as you suggested, and will let you know how it goes. As for the code you have send to me I have try using it on my program but when i select the printer and sent for printing, the form for printers will print itself instead of the crystal report that i want to print, that's why i send you the Demo program so that you see how it behave. Thanks alot!!!!.

Hi luckyCO ,

I love the effort you have been making to solving problems.

I have been working on a standard package like for 3 years now. Now, I intend to package it. And I get exceptions on missing files or .dll. I used crystal report. When I try to run setup on a virgin system, it requires Vb and crystal report for the package to run. what do you think the problems is? Thanks for your time

Jeovy:

Hi luckyCO ,

I love the effort you have been making to solving problems.

I have been working on a standard package like for 3 years now. Now, I intend to package it. And I get exceptions on missing files or .dll. I used crystal report. When I try to run setup on a virgin system, it requires Vb and crystal report for the package to run. what do you think the problems is? Thanks for your time

Use package and development wizard, ensure that third party all the third party files,dll etc are added during the wizard.
At the end, you will have 3 files including one folder.
Copy the four objects to your virgin computer and run installation process by click at Setup.exe, at the end it will work.

Hope that helps.

Hi Lucky CO,

I have a problem printing the values from my textboxes in my GUI to a plain sheet of paper in a certain order.
I actually am working on a scheduling application and would like to print out my values in a matrix order,

How exactly do you print out values from various textboxes (same form) such that they are aligned in a certain way on a sheet of plain paper?
The textboxes are many and are as much as 100.
Please publish solutions for tabular format on any arrangement you know (eg. 5 X 5 Format) and/or non-tabular formats…

I will understand.

Thanks…

zmovement:

Hi Lucky CO,

I have a problem printing the values from my textboxes in my GUI to a plain sheet of paper in a certain order.
I actually am working on a scheduling application and would like to print out my values in a matrix order,

How exactly do you print out values from various textboxes (same form) such that they are aligned in a certain way on a sheet of plain paper?
The textboxes are many and are as much as 100.
Please publish solutions for tabular format on any arrangement you know (eg. 5 X 5 Format) and/or non-tabular formats…

I will understand.



Thanks…

in vb6.0 align a value from text box might be a little bit technical.
In any case, I would like you to seed the screen shot of what you want and how you want it.
Please be details, I may not give you exact code which u need if you ask me to go by what u hv given me here.

Thanks

The below can as well help you know the textboxes in your form

dim cto as control
for each cto in me
if typeof cto is textbox then
' Arrange your data for printing
end if
next


Printer.print your data

pls i need to know how to link mysql server with vb6.0 thanks.

You must have ODBC connector download from http://dev.mysql.com/downloads/connector/odbc/3.51.html
After, you put in connection string driver={MySQL ODBC 3.51 Driver};server=localhost;database= ;uid= ;pwd=

And try again, make sure you have installed,configured your MySQL database engine.

can u pls teach me how i can link mysql server with Vb6.0. i use mysql 4 and microsoft sql server. but i've been unable to establish a reasonanble connection with each of them from vb. pls explain and i will appreciate an example. thanks so much

PLS HELP WITH CODE ON HOW TO CALCULATE TOTAL VALUES (MONEY) IN MY MS ACCESS. AND I WANT IT TO APPEAR IN MY REPORT e.g Datagrid Report

tolubonnke,

To link VB6.0 to SQL Server, use System DSN in Windows Control Panel. I have put some code together for you incorporating the System DSN called SureOne.

For this code to work, don't forget to create a System DSN called SureOne, If you need help, please ask.

Now, copy the following code and paste it on a Module within Visual Basic 6.0 and save it with any name of your choice. You can then make code calls to each of the procedures in the module passing the correct parameters and returning the correct data types to the client as required.


'Code Begin------------------

Public Function CreateClientRecordSet( _
ByVal i_strSQL As String, _
ByRef i_strError As String) As ADODB.Recordset
'+-----------------------------------------------------------------------------+
'| Purpose : This method returns a disconnected ADO recordset to be passed to
'| the client for displaying within lists
'|
'| Inputs : i_strSQL - SQL statement used to create recordset
'| i_strError - Error string
'|
'| Returns : ADO Recordset containing matching details
'|
'+-----------------------------------------------------------------------------+
Dim rsADODBRecordset As ADODB.Recordset
Dim condb As ADODB.Connection
Dim strConn As String

On Error GoTo ErrorHandler

'Open database connection
Set condb = OpenDatabaseConnection(i_strError)

'Check for open database connection error
If Trim$(i_strError) <> vbNullString Then Exit Function

'Create new ADO recordset
Set rsADODBRecordset = New ADODB.Recordset

With rsADODBRecordset

'Set cursor location to client to allow disconnected
'recordset to be returned to client
.CursorLocation = adUseClient

'Set open dynamic
.CursorType = adOpenDynamic

'Set optimistic locking
.LockType = adLockOptimistic

.ActiveConnection = condb

'Create ADO recordset
.Open i_strSQL

'Disconnect recordset from database connection
Set .ActiveConnection = Nothing

End With

'Close database connection as recordset disconnected
Call CloseDatabaseConnection(condb, i_strError)

'Check for close database connection error
If Trim$(i_strError) <> vbNullString Then Exit Function

'Return recordset to client
Set CreateClientRecordSet = rsADODBRecordset

GoExit:
Exit Function

ErrorHandler:
i_strError = i_strError & " " & "Retrieve Data Error, " & vbCrLf & vbCrLf & _
Err.Number & " " & Err.Description & " (CreateClientRecordSet)"
Call CloseDatabaseConnection(condb, i_strError)
Resume GoExit
End Function 'CreateClientRecordSet

Public Function ExecuteSQLStatement(ByVal i_strSQL As String, _
ByRef i_strError As String) As Long
'+-----------------------------------------------------------------------------+
'| Purpose : Executes a SQL statement to update or delete a number of database
'| records and returns the number of rows affected
'|
'| Inputs : i_strSQL - SQL statement to be executed
'| i_strError - Error string
'|
'| Outputs : Rows affected
'|
'+-----------------------------------------------------------------------------+
On Error GoTo ErrorHandler

Dim condb As ADODB.Connection
Dim comDB As ADODB.Command
Dim lngRecordsAffected As Long

Set condb = OpenDatabaseConnection(i_strError)

'Check for open database connection error
If Trim$(i_strError) <> vbNullString Then Exit Function

Set comDB = New ADODB.Command

'Connect command to active database connection
Set comDB.ActiveConnection = condb

comDB.CommandText = i_strSQL
comDB.CommandType = adCmdText
comDB.CommandTimeout = 40

'Execute SQL statement
comDB.Execute lngRecordsAffected

'Close database connection as SQL executed
Call CloseDatabaseConnection(condb, i_strError)

'Check for close database connection error
If Trim$(i_strError) <> vbNullString Then Exit Function

Set comDB = Nothing

'Return number of records affected
ExecuteSQLStatement = lngRecordsAffected

GoExit:
Exit Function

ErrorHandler:
i_strError = i_strError & " " & "Retrieve Data Error, " & vbCrLf & vbCrLf & _
Err.Number & " " & Err.Description & " (ExecuteSQLStatement)"
Call CloseDatabaseConnection(condb, i_strError)
Set condb = Nothing
Set comDB = Nothing
Resume GoExit
End Function 'ExecuteSQLStatement

Public Function OpenDatabaseConnection(ByRef i_strError As String) As ADODB.Connection
'+-----------------------------------------------------------------------------+
'| Purpose : This operation returns a new database connection
'|
'| Inputs : i_strError
'|
'| Returns : Open ADO database connection
'|
'+-----------------------------------------------------------------------------+
Dim condb As ADODB.Connection

On Error GoTo ErrorHandler

Set condb = New ADODB.Connection

'Setup connection using: System DSN called SureOne
With condb
.ConnectionString = "DSN=SureOne;UID=;PWD=;"
.Open
End With

'Return database connection
Set OpenDatabaseConnection = condb

GoExit:
Exit Function

ErrorHandler:
i_strError = "Open Database Error, " & vbCrLf & vbCrLf & _
Err.Number & " " & Err.Description & " (OpenDatabaseConnection)"
Resume GoExit
End Function

Public Sub CloseDatabaseConnection(ByRef i_conDB As ADODB.Connection, _
ByRef i_strError As String)
'+-----------------------------------------------------------------------------+
'| Purpose : This method disconnects the current process from the database
'|
'| Inputs : i_conDB - Open database connection
'| i_strError - Error string
'|
'| Outputs : None
'|
'+-----------------------------------------------------------------------------+
On Error GoTo ErrorHandler

'Check whether connection object has been instantiated
If Not (i_conDB Is Nothing) Then

'Close database connection
If i_conDB.State <> adStateClosed Then
Call i_conDB.Close

End If

Set i_conDB = Nothing
End If

GoExit:
Exit Sub

ErrorHandler:
i_strError = "Close Database Error, " & vbCrLf & vbCrLf & _
Err.Number & " " & Err.Description & " (CloseDatabaseConnection)"
Resume GoExit
End Sub 'CloseDatabaseConnection

'Code Ended-------------------------------


Let me know if you need help.

bigafees,

You need to use the MS Access query to set your criteria.

If you give me the names of the fields in the table, I will help you design a query with the criteria for retrieving the total values.

You can also do the same using SQL. For example:

Example 1:
SELECT MoneyValue
FROM MoneyTable
WHERE MoneyID is not NULL

Example 2:
SELECT SUM(MoneyValue) As MyTotal
FROM MoneyTable
WHERE MoneyID is not NULL


Example 2 is similar to SUM function in MS Excel.

Let me know if you need more help.

Hi Mr. LuckyCo the guru, good to you and to all the respectable members of this forum. My question is on DataGrid, though i have seen on page 7 and 8 of this thread you have respond to something close to this (i.e MSHFlexgrid) but mine is how to export data displayed  on DataGrid to Excel using vb6. I know how to display the data from the tables of my database into the Datagrid. But what i dont know is how to export it (display) to Excel. below is the sample of the code i use when displaying the data into the Datagrid which works perfectly;

[Private Sub PayLoan()
'On Error Resume Next
Dim rsPayLoan As New ADODB.Recordset

    With rsPayLoan
    .Open "Select Member_ID, Member_Name, Loan_ID, Loan_Name, Loan_Date, Monthly_Refund, Refund_Date, Total_Refund, Balance_To_Refund, Loan_Status from PayLoan ORDER BY Refund_Date ASC", Conn, adOpenDynamic, adLockOptimistic
   
        If rsPayLoan.RecordCount >= 0 Then
       
            With dtgPayLoan
           
                .ClearFields
                Set .DataSource = rsPayLoan
                .Refresh
               
                .Columns(0).Caption = "MEMB ID"
                .Columns(0).Width = 1200
                .Columns(0).Alignment = dbgCenter
                .Columns(1).Caption = "MEMB NAME"
                .Columns(1).Width = 2250
                .Columns(2).Caption = "LOAN ID"
                .Columns(2).Width = 1200
                .Columns(2).Alignment = dbgCenter
                .Columns(3).Caption = "LOAN NAME"
                .Columns(3).Width = 2500
                .Columns(4).Caption = "LOAN DATE"
                .Columns(4).Width = 1300
                .Columns(5).Caption = "MONTH RFD"
                .Columns(5).Width = 1200
                .Columns(6).Caption = "RFD_DATE"
                .Columns(6).Width = 1300
                .Columns(7).Caption = "TOTAL RFD"
                .Columns(7).Width = 1200
                .Columns(.Caption = "BAL REMAIN"
                .Columns(.Width = 1300
                .Columns(9).Caption = "LN STATUS"
                .Columns(9).Width = 1400
               
           End With
           
        End If
       
    End With
End Sub]
So pls i need your help on this, also about our previous discussion on the Encryption/Decryption of program i am still on it when the time come i will give you a call.
Thanks!!!!

madas,

The following code is a working code from one of my projects - fully compiled and working as I write. The Procedure require you to pass the Datagrid as parameter and error string is passed back by reference. To call the code below, simply type Call ProcessExcel(Datagrid, String). The code is commented to help you understand it. You can use the code below:

Public Sub ProcessExcel(ByVal i_dgdResult As DataGrid, _
ByRef i_strError As String)

On Error GoTo ErrorHandler

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim colCount As Integer
Dim oFs As New Scripting.FileSystemObject
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
Dim intCount As Integer
Dim b As Excel.Borders
Dim i As Integer
Dim XL_PATH As String
Dim strErrorMsg As String

'Validate datagrid
If i_dgdResult Is Nothing Then
Exit Sub
ElseIf i_dgdResult.ApproxCount = 0 Then
Exit Sub
End If

If MsgBox("Export these results into Excel spreadsheet?", _
vbQuestion + vbYesNo, gstrTITLE) <> vbYes Then Exit Sub
XL_PATH = oFs.GetSpecialFolder(TemporaryFolder).Path & "\my_data.xls"

strErrorMsg = "Spreadsheet is already open. " & vbCrLf & _
"Please close or save it before re-running spreadsheet."

Screen.MousePointer = vbHourglass

'kill any previous versions of the spreadsheet
If oFs.FileExists(XL_PATH) Then
Kill XL_PATH
End If

'Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application"
Set xlWb = xlApp.Workbooks.Add()
Set xlWs = xlWb.Worksheets("Sheet1"

'Copy total column count
colCount = i_dgdResult.Columns.Count

'Copy total record count
recCount = i_dgdResult.ApproxCount

'Copy column names to the first row of the worksheet
For iCol = 0 To colCount - 1
If i_dgdResult.Columns(iCol).Visible = True Then
i = i + 1
xlWs.Cells(1, i).Value = i_dgdResult.Columns(iCol).Caption
End If
Next iCol

'Set start counter to second row of worksheet
iRow = 2

'Initialise counter (used for visible datagrid columns)
i = 0

'Set initial bookmark
i_dgdResult.Bookmark = 1

For intCount = 1 To recCount
'Input values for current row in worksheet
For iCol = 0 To colCount - 1
If i_dgdResult.Columns(iCol).Visible = True Then
i = i + 1
xlWs.Cells(iRow, i).Value = i_dgdResult.Columns(iCol)
End If
Next iCol

'Initialise column counters
iCol = 0
i = 0

'Increment row counter
iRow = iRow + 1

If i_dgdResult.Bookmark < i_dgdResult.ApproxCount Then
'Increment bookmark, if not end of record
i_dgdResult.Bookmark = CVar(i_dgdResult.Bookmark + 1)
End If
Next intCount

'Reset bookmark to first record
i_dgdResult.Bookmark = 1

'Format worksheet
With xlWs
.Select
.Columns.BorderAround
.Columns.AutoFit
End With

'Save worksheet
xlWb.SaveAs XL_PATH

'Make Excel application visible to user
xlApp.Visible = True

'Clean Up - Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Set i_dgdResult = Nothing

Screen.MousePointer = vbDefault
Exit Sub

ErrorHandler:
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Set i_dgdResult = Nothing
Screen.MousePointer = vbNormal
'Permission denied - On deleting spreadsheet file
If Err.Number = 70 Then
i_strError = strErrorMsg
Exit Sub
End If
i_strError = Err.Number & " " & Err.Description & " ProcessExcel())"
Exit Sub
End Sub

See code . long like hell.

I dont normally use datagrid in vb6.0 because of its inflexibility. It is MS problem, they have corrected the problem in vb2005,2008.

There are things I need to with little code but datagrid would require 100 of lines of code, so I dont always use it.
I hope the assistance offered by beknown can help you get through the problem.

Should incase it didnt solve it then I hv to code it up to verify what you want and possibly give you the solution.

beknown:

madas,

The following code is a working code from one of my projects - fully compiled and working as I write. The Procedure require you to pass the Datagrid as parameter and error string is passed back by reference. To call the code below, simply type Call ProcessExcel(Datagrid, String). The code is commented to help you understand it. You can use the code below:

Public Sub ProcessExcel(ByVal i_dgdResult As DataGrid, _
ByRef i_strError As String)

On Error GoTo ErrorHandler

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim colCount As Integer
Dim oFs As New Scripting.FileSystemObject
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
Dim intCount As Integer
Dim b As Excel.Borders
Dim i As Integer
Dim XL_PATH As String
Dim strErrorMsg As String

'Validate datagrid
If i_dgdResult Is Nothing Then
Exit Sub
ElseIf i_dgdResult.ApproxCount = 0 Then
Exit Sub
End If

If MsgBox("Export these results into Excel spreadsheet?", _
vbQuestion + vbYesNo, gstrTITLE) <> vbYes Then Exit Sub
XL_PATH = oFs.GetSpecialFolder(TemporaryFolder).Path & "\my_data.xls"

strErrorMsg = "Spreadsheet is already open. " & vbCrLf & _
"Please close or save it before re-running spreadsheet."

Screen.MousePointer = vbHourglass

'kill any previous versions of the spreadsheet
If oFs.FileExists(XL_PATH) Then
Kill XL_PATH
End If

'Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application"
Set xlWb = xlApp.Workbooks.Add()
Set xlWs = xlWb.Worksheets("Sheet1"

'Copy total column count
colCount = i_dgdResult.Columns.Count

'Copy total record count
recCount = i_dgdResult.ApproxCount

'Copy column names to the first row of the worksheet
For iCol = 0 To colCount - 1
If i_dgdResult.Columns(iCol).Visible = True Then
i = i + 1
xlWs.Cells(1, i).Value = i_dgdResult.Columns(iCol).Caption
End If
Next iCol

'Set start counter to second row of worksheet
iRow = 2

'Initialise counter (used for visible datagrid columns)
i = 0

'Set initial bookmark
i_dgdResult.Bookmark = 1

For intCount = 1 To recCount
'Input values for current row in worksheet
For iCol = 0 To colCount - 1
If i_dgdResult.Columns(iCol).Visible = True Then
i = i + 1
xlWs.Cells(iRow, i).Value = i_dgdResult.Columns(iCol)
End If
Next iCol

'Initialise column counters
iCol = 0
i = 0

'Increment row counter
iRow = iRow + 1

If i_dgdResult.Bookmark < i_dgdResult.ApproxCount Then
'Increment bookmark, if not end of record
i_dgdResult.Bookmark = CVar(i_dgdResult.Bookmark + 1)
End If
Next intCount

'Reset bookmark to first record
i_dgdResult.Bookmark = 1

'Format worksheet
With xlWs
.Select
.Columns.BorderAround
.Columns.AutoFit
End With

'Save worksheet
xlWb.SaveAs XL_PATH

'Make Excel application visible to user
xlApp.Visible = True

'Clean Up - Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Set i_dgdResult = Nothing

Screen.MousePointer = vbDefault
Exit Sub

ErrorHandler:
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Set i_dgdResult = Nothing
Screen.MousePointer = vbNormal
'Permission denied - On deleting spreadsheet file
If Err.Number = 70 Then
i_strError = strErrorMsg
Exit Sub
End If
i_strError = Err.Number & " " & Err.Description & " ProcessExcel())"
Exit Sub
End Sub

Hi Beknown Thank you very much for your quick response to my question and Mr luckyco i thanks as well. As regards to above code you sent i Coppied and paste it  on my program, and i then created command button and set it as a click event, and insert the code "Call ProcessExcel(Datagrid, String)", But when i try to RUN the program its giving me error message as compile error "Expected: (" at the end of this line code the call Process thats after the word string) the close bracket is Hilighted in blue, when i put the open bracket "(" in place of the ")" it will demand me to write something like "Number As long,Character", when i write this another error message will pop Up as "Expected: list separator" with "As" being hilighted in blue. And if i made call statement without "(Datagrid, String)", that's "Call ProcessExcel" this error message will pop Up "Argurement not optional" with the "Call ProcessExcel" being Hilighted in blue, when right click on the Hilighted potion and select "Definition" it will take me to this line of the code "Public Sub ProcessExcel(ByVal i_dgdResult As DataGrid, ByRef i_strError As String)".  So pls how do i take care of this error? and make work.

NB; When i coppy and paste that code in my program i made some modification in order for the code to suit into the definition of my program, like "i_dgdResult" i changed it  to dtgpayloan which is the name i gave to the datagrid in my program and some other little changes which i known are necessary in order to make my program in consunance with your code.

madas,

The call to ProcessExcel procedure expects two arguments - Datagrid and String.

The datagrid must contain real data for export to Microsoft Excel. It is not enough to have an empty datagrid and a command button on the client screen.

The following validation code is modified:

'Validate datagrid
If i_dgdResult Is Nothing Then
i_strError = "Empty datagrid."
Exit Sub
ElseIf i_dgdResult.ApproxCount = 0 Then
i_strError = "No records for export."
Exit Sub
End If


To run the code successfully, the datagrid must contain real data. If you still have a problem with this, please feedback here.

beknown:


madas,
The call to ProcessExcel procedure expects two arguments - Datagrid and String.

The datagrid must contain real data for export to Microsoft Excel. It is not enough to have an empty datagrid and a command button on the client screen.

The following validation code is modified:

'Validate datagrid
If i_dgdResult Is Nothing Then
i_strError = "Empty datagrid."
Exit Sub
ElseIf i_dgdResult.ApproxCount = 0 Then
i_strError = "No records for export."
Exit Sub
End If


To run the code successfully, the datagrid must contain real data. If you still have a problem with this, please feedback here.

Beknown;

Thank you very much for quick resfond to my question. I have corrected the validation code and command button section where I "Call processExcel(datagrid, string)", i was able trace the problem and rectify it and code work perfectly.
Once again thank you very much for that wonderful and working CODE. But another thing is that how do insert the caption (Heading) of my datagrid on Excel sheet so that when i print the data, the heading will appear?. Because now when i printed the data the heading did not appear.

madas,

Notice that I have modified the formating code for the worksheet in the ProcessExcel procedure. The change is shown in bold:

'Format worksheet
With xlWs
.PageSetup.RightHeader = i_dgdResult.Caption
.Select
.Columns.BorderAround
.Columns.AutoFit
End With

Also, you can change RightHeader to LeftHeader or CenterHeader depending on where you want the header to appear in your printout.

And there are more formatting for the worksheet footer below if you are interested, following the same style for header above:
.LeftFooter = "Text Here"
.CenterFooter = "Text Here"
.RightFooter = "Text Here"

beknown:

madas,

Notice that I have modified the formating code for the worksheet in the ProcessExcel procedure. The change is shown in bold:

'Format worksheet
With xlWs
.PageSetup.RightHeader = i_dgdResult.Caption
.Select
.Columns.BorderAround
.Columns.AutoFit
End With

Also, you can change RightHeader to LeftHeader or CenterHeader depending on where you want the header to appear in your printout.

And there are more formatting for the worksheet footer below if you are interested, following the same style for header above:
.LeftFooter = "Text Here"
.CenterFooter = "Text Here"
.RightFooter = "Text Here"

Beknown;
Thank you very much for your quick resfond to my Question. The CODE really work perfectly. thanks once again for the wonderful assistance you people are rendering to beginners like us.

No more questions?

Pls i need code to export record in my Ms Access linking to vb 6 to Ms Excel
thanks

I have written his code before but I will write it again.

Function below copied data from MS Access to MSHFlexGrid

Public sub FillInMSH(MSH1 As MSHFlexGrid, DbPath As String, SQLstr As String)
On Error GoTo errh

Dim J As Long, I As Integer, db As New ADODB.Recordset
Dim rs As New ADODB.Connection
rs.ConnectionString = DbPath
rs.Open
Set db = rs.Execute(SQLstr)

MSH1.Refresh
Cancel_Grid_Fill = False


MSH1.Rows = 2

MSH1.Cols = db.Fields.Count
For J = 0 To db.Fields.Count - 1
MSH1.TextMatrix(0, J) = db.Fields(J).Name
MSH1.TextMatrix(1, J) = ""
Next J

J = 0

Do While Not db.EOF
J = J + 1
MSH1.Rows = J + 1

For I = 0 To db.Fields.Count - 1
MSH1.TextMatrix(J, I) = ""

If IsNull(db.Fields(I).Value) = False Then
MSH1.TextMatrix(J, I) = " " & db.Fields(I).Value
end if
Next I

db.MoveNext
DoEvents

Loop


db.Close
rs.Close

Exit sub

errh:
End sub



The code below will export MSHFlexgrid to excel


Public Sub PrintMsh(Mhs As Control, Optional KIM As Integer)

If MsgBox("Are you sure you want to export the data to excel", vbQuestion + vbYesNo, "Choose either yes or no" = vbNo Then Exit Sub

On Error GoTo errh
Dim D As Object, K As Long, J As Long
Set D = CreateObject("Excel.Application"
D.Visible = True
D.Workbooks.Add
Dim II As Long
Dim i9 As Long

Dim i8 As Integer
i8 = CInt(Mhs.Rows / 65536) - 3
For K = 1 To i8
D.Worksheets.Add
Next K

i8 = 1
i9 = 65536
II = 1
Dim M As Long
M = 1
Dim DF0 As Integer
If KIM <> 0 Then DF0 = KIM + 1

If KIM = 0 Then DF0 = KIM + 1

For K = 0 To Mhs.Rows - 1
If K = i9 Then
II = II + 1: i9 = i9 + 65536: M = 1
If II = i8 Then i8 = i8 + 1:

D.Worksheets(II).Columns.Font.Size = 8
D.Worksheets(II).Rows(1).Font.Bold = True
D.Worksheets(II).Rows.AutoFit
D.Worksheets(II).Columns.AutoFit
End If



For J = 1 To Mhs.Cols - DF0

D.Worksheets(II).Cells(M, J + 1) = Trim(Mhs.TextMatrix(K, J))

Next J

D.Worksheets(II).Cells(M, 1) = M - 1
M = M + 1
Next K

D.Worksheets(II).Cells(1, 1) = "S/N"

D.Worksheets(II).Columns.Font.Size = 8
D.Worksheets(II).Rows(1).Font.Bold = True
D.Worksheets(II).Rows.AutoFit
D.Worksheets(II).Columns.AutoFit
Exit Sub
errh:
End Sub

How to call them


Private Sub cmdFetchData_Click()
FillInMSH <your mshflexgrid>,connectionstring,sqlstatement)
End Sub

Private Sub cmdExportData_Click()
PrintMsh <your mshflexgrid>
End Sub


Note please check and if you find any error please correct, I copied and edited it here.

Try the open source website:

http://sourceforge.net/

Good!
No more questions?

Hi all the gurus in the house i salut you. pls i have a small problem with date(datetime) stamping on my program. You see i have some table(four) that are storing some related information that i want one of the table(the fouth table) to use the three other tables to updates itself any time any one of those three table are altererd(updated), and i want anytime the fouth table is update, the system Date or the present date should be saved on a column to the coresponding row. But i am not getting that. This is what i do;

[Dim StrDate As String or
Dim StrDate AS Date
StrDate = Format(Now, "YYYY-DD-MM"]
Then at the vbmysql code side i have
!Date = StrDate
But the problem is that the program when runned will pick the present system Date and saved it as needed, but when but anothe event occur on that same day(date) the program will not saved date (sameDate) but rather it will saved time (12:00:00AM) to the coresponding row, and so it will continue depending on the number of Update perform that day, and when you come the next day the first update of that day will pick date but the remaining will be time.
So pls help because the above did not give exactly what i want. i even try [strDate = Format(SystemTime, "YYYY-DD-MM"] but is not picking any date.

madas:

Hi all the gurus in the house i salut you. pls i have a small problem with date(datetime) stamping on my program. You see i have some table(four) that are storing some related information that i want one of the table(the fouth table) to use the three other tables to updates itself any time any one of those three table are altererd(updated), and i want anytime the fouth table is update, the system Date or the present date should be saved on a column to the coresponding row. But i am not getting that. This is what i do;

[Dim StrDate As String or
Dim StrDate AS Date
StrDate = Format(Now, "YYYY-DD-MM"]
Then at the vbmysql code side i have
!Date = StrDate
But the problem is that the program when runned will pick the present system Date and saved it as needed, but when but anothe event occur on that same day(date) the program will not saved date (sameDate) but rather it will saved time (12:00:00AM) to the coresponding row, and so it will continue depending on the number of Update perform that day, and when you come the next day the first update of that day will pick date but the remaining will be time.
So pls help because the above did not give exactly what i want. i even try [strDate = Format(SystemTime, "YYYY-DD-MM"] but is not picking any date.

Hi gurus in the house am sorry i have traced where the problem was and i have corrected it. the above i.e; [Dim StrDate As String or
Dim StrDate AS Date
StrDate = Format(Now, "YYYY-MM-DD"] has worked.
Thannks Aloooooooooooooooooooooooooooooooottttttttttttttttttttttttttttt!!!!!!!!!!!!!!!!!!!!!!!!!.

Hi all, pls supposing i have a table that store some data at mysql side, if for example i have input some info that update the table to about more than 100 row, and i want perform a search of the last row and display the info on datagrid on my form and i want use the Last Row as the condition. How do i go about it. I use this;
[.Open "Select * From accountstate where Account_Balance = (Select LAST(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] and is giving error messge. But when I search for Maximum Value using this:
[.Open "Select * From accountstate where Account_Balance = (Select MAX(Account_Balance) from accountstate)", Conn, adOpenDynamic, adLockOptimistic] it works perfectly.
NOTE: am interested on the info in the Account Balance column thats why i put the column heading.
pls guys i need you help.

Thanks in advance.