|
I need a vb6 code to read an excel file, determine the number of columns in the first row and transform the columns into a table field.
I want to create a table on the fly using the excel column as fields in a table and now read the remaining rows from excel file as data into the craeted table
thanks for your anticipated cooperatio
t.aransiola
|
|
|
|
|
|
Here is the code I have:
Publicly declare
Public cnnXLS As ADODB.Connection 'ADO connection to MS-Excel
This function creates an ADO connection to MS_Excel
Public Function Set_XLS_Connection(strProv As String, strDS As String) As Boolean
On Error GoTo Set_XLS_Connection_Err
Set cnnXLS = New ADODB.Connection
With cnnXLS
.Provider = strProv
.ConnectionString = "Data Source= " & strDS & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
.Open
End With
If cnnXLS.State = 1 Then
Set_XLS_Connection = True
Else
Set_XLS_Connection = False
End If
Set_XLS_Connection_Exit:
Exit Function
Set_XLS_Connection_Err:
Msg = "Error in Startup module - Set_Database_Connection function. " & vbCr & _
"Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & vbCr & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Set_XLS_Connection = False
Resume Set_XLS_Connection_Exit
End Function
This subroutine lists all the worksheets in the selected Excel file.
Private Sub List_Excel_Worksheets(cnnXLS As ADODB.Connection)
'Purpose: Creates a list of worksheets from the selected Excel file
'Parameters: cnnXLS as an ADODB connection
'Returns: Nothing
On Error GoTo List_Excel_Worksheets_Err
Dim strSheet As String
cboWorksheet.Clear
Set rstSchema = cnnXLS.OpenSchema(adSchemaTables)
With rstSchema
Do Until .EOF
If (UCase(!TABLE_TYPE) = "ACCESS TABLE") Or (UCase(!TABLE_TYPE) = "SYSTEM TABLE") Then
.MoveNext
Else
strSheet = !TABLE_NAME
cboWorksheet.AddItem strSheet
.MoveNext
End If
Loop
End With
List_Excel_Worksheets_Exit:
On Error Resume Next
rstSchema.Close
Set rstSchema = Nothing
Exit Sub
List_Excel_Worksheets_Err:
Msg = "Error in frmTextual - List_Excel_Worksheets subroutine. " & vbCr & _
"Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & vbCr & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Resume List_Excel_Worksheets_Exit
End Sub
This subroutine writes the selected excel worksheet and places it into a FlexGrid.
Private Sub Populate_flxSource_With_Excel_Data(strWrkSht As String)
On Error GoTo Populate_flxSource_With_Excel_Data_Err
Dim I As Integer, nFlds As Integer
Dim X As Integer, Y As Integer
Dim varValue As Variant
If flxSource.Visible = False Then flxSource.Visible = True
If flxTarget.Visible = False Then flxTarget.Visible = True
Set rstXLS = New ADODB.Recordset
rstXLS.Open strWrkSht, cnnXLS, adOpenStatic, adLockOptimistic
With flxSource
.Clear
.FixedCols = 1
.FixedRows = 1
.Cols = intCols(cnnXLS, strWrkSht)
.Rows = intRows(cnnXLS, strWrkSht)
For I = .FixedRows To .Rows - 1
.TextArray(Fgi(I, 0)) = I
Next I
.ColWidth(0) = 600
.ColAlignmentFixed = flexAlignCenterCenter
'Locate the first row under the fixed row
Y = .TopRow
End With
nFlds = intFlds(cnnXLS, strWrkSht)
Call Source_Field_Hdrs(nFlds)
'Retrieve data from the excel workbook
Do While Not rstXLS.EOF
With flxSource
'Locate the first column after the fixed column
X = .LeftCol
For I = 0 To nFlds - 1
If IsE(rstXLS.Fields(I).Value) Then
varValue = Empty
Else
varValue = rstXLS.Fields(I).Value
End If
.Col = X
.Row = Y
.Text = varValue
X = X + 1
Next I
rstXLS.MoveNext
End With
Y = Y + 1
Loop
Call Target_Field_Hdrs(intRows(cnnXLS, strWrkSht), intCols(cnnXLS, strWrkSht), arryElements)
Populate_flxSource_With_Excel_Data_Exit:
On Error Resume Next
rstXLS.Close
Set rstXLS = Nothing
Exit Sub
Populate_flxSource_With_Excel_Data_Err:
Msg = "Error in frmTextual - Populate_flxSource_With_Excel_Data subroutine. " & vbCr & _
"Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & vbCr & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Resume Populate_flxSource_With_Excel_Data_Exit
End Sub
I hope this helps
Quecumber256
|
|
|
|
|
Hello,
When my combobox loads with the dataset, my textbox control shows only the first record. How can I set the code below so that as I pick a customer name from the combobox, it also shows the same customer name in my textbox?
<br />
Me.cboSelectDealer.Enabled = True<br />
Me.TblCustomerTableAdapter.Fill(Me.CustomerDataset.tblCustomer)<br />
<br />
Me.cboSelectDealer.DataSource = CustomerDataset.Tables("tblCustomer")<br />
Me.cboSelectDealer.DisplayMember = "Cust_Name"<br />
Me.cboSelectDealer.ValueMember = "ID"<br />
<br />
Me.txtCustomerName.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CustomerDataset, "tblCustomer.Cust_Name"))
Thanks in advance,
Rashar
|
|
|
|
|
<br />
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged<br />
'show the selected customer name in a textbox<br />
TextBox1.Text = ComboBox1.Text<br />
End Sub<br />
i hope this is what you meant.
|
|
|
|
|
Almost...
My form has more than one textbox control, for example, txtCustomerName, txtAddress, txtCity, txtEmail.
At design time I can set the binding source to each control's text property as such:
txtCustomerName I would set to - TblCustomerBindingSource - Cust_Name
txtAddress I would set to - TblCustomerBindingSource - Address
txtCity I would set to - TblCustomerBindingSource - City
So all my controls would look at their respective dataset, so when I scroll through my combobox, if I picked customer A then I would see the address, city, email etc. for customer a in my textboxes...subsequently if I chose customer B, I would see info for customer B etc.
I need to set this up manually, or unless there is a way that I can have multiple bindings to a textbox control because I also need to have those controls look at another table's / bindingsource.
Thanks.
|
|
|
|
|
Hello.
I think its silly question but i expect some solution .
Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.
Please, if somebody have some solution then guide me in detail.
Thanks in adv.
|
|
|
|
|
Using SQL Script to Create DB, Tables, and Relationships. Then Move the data.
|
|
|
|
|
I have a series of Reports that need to run from an ASP.NET website. So I'm creating 2 reporting classes.
1. Basic Reports
2. ReportLineItem
I'll need to overload the Basic report for the additional:
a. Financial
b. Geographical
c. Comparative Cost
Type reports.
And may need to overload the ReportLineItem with more detailed field's and calculations. The scope of the reports at this time is unknown. My question is how to us inheritance or implements within theses reporting classes to help me keep them flexable.
In the end the data mined and stored within these classes will be outputed into relational XML and formated with XSL-FO to produce PDF's. Any thoughts, help, or pass experances that may keep me from making a huge mistake is what I'm looking for.
Thanks.
|
|
|
|
|
i have two identical datagrids in my winform:
in one of them i have a vertical scrollbar that has to scoll the datagrids both and put them in the same position
well i can i do it with VS2003
in class mydatagrid
**************************************
ublic Class mydatagrid
Inherits DataGrid
Public Function getPositionScoll() As Integer
Dim pt As New Integer
If Not Me.DataSource Is Nothing Then
pt = Me.VertScrollBar.Value
End If
Return pt
End Function
Public Sub setPositionScoll(ByVal pt As Integer)
If Not Me.DataSource Is Nothing Then
Me.VertScrollBar.Value = pt
End If
End Sub
end class
*************************
in my winform
Private Sub DataGrid1_Scroll(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGrid1.Scroll
Dim ttt As Integer = DataGrid1.getPositionScoll()
DataGrid2.setPositionScoll(ttt)
End Sub
and the datagrid2 does not move
SECOND TRY
in class mydatagrid
**************************************
ublic Class mydatagrid
Inherits DataGrid
Sub ScrollToRow(ByVal row As Integer)
If Not Me.DataSource Is Nothing Then
Me.GridVScrolled(Me, New ScrollEventArgs(ScrollEventType.LargeIncrement, row))
End If
End Sub
end class
*************
in my winform
Private Sub DataGrid1_Scroll(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGrid1.Scroll
DataGrid2.ScrollToRow( an integer that you want)
End Sub
**********
now datagrid1's scrollbar moves datagrid2 to the the row but it moves only at that row...
the idea is in the scroll mathod event get the point of the scroll and than take the number of the first visible row of the datagrid1 and give it to DataGrid2.ScrollToRow:
For Each c As Control In DataGrid1.Controls
If c.GetType() Is GetType(VScrollBar) Then
pt = VScrollBar.MousePosition()
End If
Next
using pt point can i do what i want????
is this the right way to do it????
are there other ways to tune the position of my two datagrid with scroll????
|
|
|
|
|
Can I load svg image into the picturebox in vb.net application? And how do I load the image from a certain location? Could someone show me the code?
Thanks
|
|
|
|
|
How to create a setup for application incuding odbc setup,
for project done in Visual Basic 6.0
(ie with out manually creating odbc on target machine)
The setup should install application as well as odbc on target machine
for project Visual Basic 6.0
Thanks in Advance!
|
|
|
|
|
Hi,
is it possible to create an object by its class name? I want to handle some objects (in my situation these are diffent window form classes) in a manager class, but i don´t want to create every window "by hand" like this:
Dim win as new CWindow1<br />
ManagerClass.RegisterWindow(win)<br />
<br />
Dim win2 as new CWindow2<br />
ManagerClass.RegisterWindow(win2)
What i want is something like this:
ManagerClass.CreateObject("CWindow1") <br />
ManagerClass.CreateObject("CWindow2")
The object handling should be completely managed by the ManagerClass.
I found some information about the Activator class, but i don´t know if its possible to use it for my problem
Thanks for your help.
greetz
|
|
|
|
|
Create a Type object from the class name, use the GetConstructor method to get a ConstructorInfo object, and use it's Invoke method to create the object.
---
b { font-weight: normal; }
|
|
|
|
|
Hi All i have no experience of working in vb or vb dot net although i have worked a little bit in C# dot net.
now i have to access a database using VB.Net the database i m using is ms access so plzz anyone tell me that wat is the best and easier to implement way of doing it
Looking forward for help,
Regards,
|
|
|
|
|
|
Thxx Sir, it was really very helpful and written in very easy to understand manner....thxx once again 
Regards,
|
|
|
|
|
Hi all!
I need help in getting information from a MS Project file via vb.net application.Can any body deliever me the documentation and some sample code for help please.
Thanks in advance,
shani
|
|
|
|
|
dear sir, will u please help me how to disable the main menu when all its child menus are disabled.i am also sending the code what i have done but it is not working effectively.some menus will get disabled and some wont.please help me out regarding this problem.
Sub sbSubMenu(ByVal mnu As MenuItem)
Dim boolVisible As Boolean
Try
Dim intIndex As Integer
Dim intInnerIndex As Integer
For intIndex = 0 To mnu.MenuItems.Count - 1
If mnu.IsParent = True Then
For intInnerIndex = 0 To mnu.MenuItems.Count - 1
boolVisible = True = False
If mnu.MenuItems(intInnerIndex).Visible Then
mnu.Visible = True
boolVisible = True
Exit For
Else
mnu.Visible = False
End If
Next
If boolVisible Then Call sbSubMenu(mnu.MenuItems(intIndex))
End If
Next
Catch ex As Exception
Call fnErrorLog(objFrmMain.Name, ex.Message, " frmMain_Load Block")
End Try
End Sub
|
|
|
|
|
first of all i m unable to follow this statement( may causing problems)
avinashprabhu.t.p wrote: boolVisible = True = False
shani
|
|
|
|
|
The statement is a bit more readable in this form:
boolVisible = (True = False)
As the two operands are not equal, the (True = False) comparison will result in the value False. That means that the code does the same as:
boolVisible = False
---
b { font-weight: normal; }
|
|
|
|
|
Hi,
I'm working on VB.Net (Window Base), I have add crystal report in my application. When I make a setup file in my own PC and I have try to run the exe file, it seem work nice on my own PC. Then I try to run the setup in other machine, and try to run the exe as well, when I open the report from it, it give me a error msg. The error msg are look like this 'Cannot find keycodev2.dll or invalid keycode.'
Can anyone teach me step by step to slove this error? Thanks.
|
|
|
|
|
it's actually quite simple. assuming you've added a setup project to your solution, what you gotta do is
1. right click on the setup project in solution explorer and go to "
Add > Merge Module..."
2. add the Crystal_regwiz2003.msm to your project.
3. to get your license key, click on "Help > About Microsoft Development Environment", click on the list box item for crystal reports and then click on the "copy info" button.
5. now right click on Crystal_regwiz2003.msm in solution explorer and select "Properties".
6. expand the (MergeModuleProperties) node in the properties window. you should see a "License Key" property. Paste the license key that you obtained from step 3 (remember to delete the other junk that was copied with it).
to explain a bit... (from MSDN)
Crystal_regwiz2003.msm configures registration information on deployed computers (server or client) in order to track licensing information.
When Crystal_regwiz2003.msm is added to a setup project, it exposes a LicenseKey property that must be set before building the setup project.
The license key is emailed to you when the product is registered. This key is also available from the About Microsoft Visual Studio .NET dialog box from the Help menu. Enter this license key into the LicenseKey property for the Crystal_regwiz2003.msm merge module.
Note: If the license key is not set for the Crystal_regwiz2003.msm merge module, an error will occur when building the project. If the setup is installed on a target computer without specifying the license key for the merge module, various keycodedev2.dll errors will occur.
---
sorry for the long explanation.
|
|
|
|
|
In the step 6, it is I only need to paste the 19 digit inside the license key? After paste the license key, I have try to build the setup by right click the setup file at the solution explorer and choose build. After success everyting, I have copy the setup file and try to install at other machine, but the error still come out? What mistake I have make? Can give some advice?
|
|
|
|
|
have you also included the correct merge module for your database type?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
