Student_101
asked on
Import certain columns from Excel to Access in VB6
Hi Experts,
I need to import certain columns from Excel, using VB6, into Access.
In the first component of my application, I import text files, and call the "DoCmd.RunCommand acCmdImport" from VB6, as the files I import are fixed width files.
Now I need to import an excel file, but the problem is that I don't need the whole file, I just need some columns. Another issue is that in some excel files, those columns change.
To tackle this, I thought of making text boxes in VB6, in which the user can enter column names, such as R,S,T, in which the data needed is.
So now I need to only import those columns into my access database, using the ADODB connection and recordset.
I haven't done this before, importing only certain columns, so any help will be appreciated !!
Thanks if advance.
I need to import certain columns from Excel, using VB6, into Access.
In the first component of my application, I import text files, and call the "DoCmd.RunCommand acCmdImport" from VB6, as the files I import are fixed width files.
Now I need to import an excel file, but the problem is that I don't need the whole file, I just need some columns. Another issue is that in some excel files, those columns change.
To tackle this, I thought of making text boxes in VB6, in which the user can enter column names, such as R,S,T, in which the data needed is.
So now I need to only import those columns into my access database, using the ADODB connection and recordset.
I haven't done this before, importing only certain columns, so any help will be appreciated !!
Thanks if advance.
So you are automating an Access database from within a Visual Basic application?
ASKER
Yes, that's correct for the 1st part.
I don't think I need to do it for the excel files, because if the user specifies which columns they need, then I think automating will not be needed, and automating was needed because the earlier files were fixed width text files, but that wont be needed in excel :)
We just need to get those columns, that are specified in the text box, into an access database.
I don't think I need to do it for the excel files, because if the user specifies which columns they need, then I think automating will not be needed, and automating was needed because the earlier files were fixed width text files, but that wont be needed in excel :)
We just need to get those columns, that are specified in the text box, into an access database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmm.
I know how to connect to my database, but will I have to do the same connection to an Excel file?
for example:
ConnectionString = "Data Source=C:\MyFolder\MyWorkb ook.xls;" & _
"Extended Properties=Excel 8.0;"
This is how I add something to my table in the database
RS_AddToNew.AddNew
RS_AddToNew!xyz = xyz
RS_AddToNew!Row = value
so in for excel, the column headers are R,S,T
So would I do something like RS_AddToNew!Field = R
?
I know how to connect to my database, but will I have to do the same connection to an Excel file?
for example:
ConnectionString = "Data Source=C:\MyFolder\MyWorkb
"Extended Properties=Excel 8.0;"
This is how I add something to my table in the database
RS_AddToNew.AddNew
RS_AddToNew!xyz = xyz
RS_AddToNew!Row = value
so in for excel, the column headers are R,S,T
So would I do something like RS_AddToNew!Field = R
?
''' This is how I connect to my Access Database
Public Function Connection()
If (Conn.State = 1) Then m_Conn.Close ' Close connection to Database
m_Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\Database.mdb"
Conn.CursorLocation = adUseClient
Conn.Open
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the misunderstanding,
but I mentioned earlier that the columns were not fixed, in some files I need information from columns R,S,T. While in others, some other columns, and that's why I have the text boxes so that the user can input the column headers.
Secondly, the data begins from row 11, so by automating, will we be able to select which columns we want? If yes, then that is more user friendly.
but I mentioned earlier that the columns were not fixed, in some files I need information from columns R,S,T. While in others, some other columns, and that's why I have the text boxes so that the user can input the column headers.
Secondly, the data begins from row 11, so by automating, will we be able to select which columns we want? If yes, then that is more user friendly.
ASKER
I Just tried automating the process, and it wont work out, because there are more then 35 columns, and I only need 5-6 of them.
So it will take to long to skip, I need a way to just import that columns based on the column headers that the user types in the text boxes.
So your code earlier will work in that case,
but what about the rows not starting at 1.
They are fixed at row 15 for each file.
So it will take to long to skip, I need a way to just import that columns based on the column headers that the user types in the text boxes.
So your code earlier will work in that case,
but what about the rows not starting at 1.
They are fixed at row 15 for each file.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yea, that's right.
I get that part, but you said that it will import directly everything from row 1.
I only want the information from row 15.
I get that part, but you said that it will import directly everything from row 1.
I only want the information from row 15.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok.
I tried out that link, and it describes in detail how to use Excel from VB6, such as how to export data and make graphs.
I will work on getting data IN from excel to VB6, in my access database, so I can show it on my flex grid.
I will get back to shortly :)
Thanks for your help.
I tried out that link, and it describes in detail how to use Excel from VB6, such as how to export data and make graphs.
I will work on getting data IN from excel to VB6, in my access database, so I can show it on my flex grid.
I will get back to shortly :)
Thanks for your help.
ASKER
I am getting an error when I try to open the file. The error is.
"No Value given for one or more required parameters".
I have no parameters in my excel worksheet, neither does it ask me for any parameters when I open the file.
I have 5 worksheets in the file, but I specified the name of the worksheet.
My code is as follows:
"No Value given for one or more required parameters".
I have no parameters in my excel worksheet, neither does it ask me for any parameters when I open the file.
I have 5 worksheets in the file, but I specified the name of the worksheet.
My code is as follows:
Private Sub cmd_Import_Click()
Dim cn As ADODB.Connection
Dim sSql As String
Set cn = New ADODB.Connection
With cn
If .State = 1 Then .Close
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & cbo_fileName.Text & ";Extended Properties=""Excel 8.0;"""
.Open
End With
sSql = "SELECT B, C, D, E," & txt_EC.Text & "," & txt_OC.Text & "," & txt_Tc.Text & " FROM [2FinalOCECCon$B15:T79]"
Dim cmd_FromExcel As New ADODB.Command
Dim RS_FromExcel As New ADODB.Recordset
With cmd_FromExcel
.ActiveConnection = cn
.CommandText = sSql
.CommandType = adCmdText
' .Parameters(0) = 1 ''' I tried this for the 7 columns, but then
' .Parameters(1) = 1 ''' I just end up with 1 for each of the colums
' .Parameters(2) = 1 ''' And there is no data left
' .Parameters(3) = 1
' .Parameters(4) = 1
' .Parameters(5) = 1
' .Parameters(6) = 1
End With
With RS_FromExcel
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd_FromExcel
End With
'Set grd_result.DataSource = RS_Results
OpenConnection
Dim cmd_ToAccess As New ADODB.Command
Dim RS_ToAccess As New ADODB.Recordset
With cmd_ToAccess
.ActiveConnection = m_Conn
.CommandText = " Select * From tbl_SampleIntegration;" 'tbl_Operation.Date;"
.CommandType = adCmdText
End With
With RS_ToAccess
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd_ToAccess
End With
Do While Not RS_FromExcel.EOF
RS_ToAccess.AddNew
RS_ToAccess!StartDate = RS_FromExcel!B
RS_ToAccess!StartTime = RS_FromExcel!C
RS_ToAccess!EndDate = RS_FromExcel!D
RS_ToAccess!EndTime = RS_FromExcel!E
RS_ToAccess!OC = RS_FromExcel.Fields(txt_OC.Text)
RS_ToAccess!EC = RS_FromExcel.Fields(txt_EC.Text)
RS_ToAccess!TC = RS_FromExcel.Fields(txt_Tc.Text)
RS_FromExcel.MoveNext
Loop
End Sub
ASKER
Are you sure that the SQL recognizes B,C,D,E as being the columns from Excel ??
ASKER
Ok
It doesn't recognize the B, C, D as Excel columns
I tried this SQL
sSql = "SELECT * from [Sheet1$];"
and the paremeters error didn't show up, but the error was, "item cannot be found in the collection...."
It doesn't recognize the B, C, D as Excel columns
I tried this SQL
sSql = "SELECT * from [Sheet1$];"
and the paremeters error didn't show up, but the error was, "item cannot be found in the collection...."
If those are actual names of your Columns, it will ... if you're referring to the internal column names, then I'm not sure ... as I said, if you care unable to open it this way, the only other method you'd have would be to automate Excel and, as stated earlier, this can be a very daunting task (and is the reason why there are so many Export/Import utilities out there).
ASKER
hmmm,
I understand your point. I was able to start with row 15, so that's not a problem now.
Yes, B,C.. were the internal names of the columns in excel, and VB6 doesn't recognize it, so I used rst.fields(0), and it gave me the correct value.
Now for the one's that I have in my textbox, is there a way to convert the letter into the corresponding number?
I tried Cint, and Int, but they didn't work.
I understand your point. I was able to start with row 15, so that's not a problem now.
Yes, B,C.. were the internal names of the columns in excel, and VB6 doesn't recognize it, so I used rst.fields(0), and it gave me the correct value.
Now for the one's that I have in my textbox, is there a way to convert the letter into the corresponding number?
I tried Cint, and Int, but they didn't work.
Not that I'm aware ... you'd need some sort of "mapping" table, like A=1, B=2 etc etc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.