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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you are automating an Access database from within a Visual Basic application?
Student_101Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't need to automate Access for this ... you can read the Excel file directly via an ADO recordset:

From there, you could use the Fields collection of that recordset to determine exactly which columns to import, then just move them to Access, again using an ADO (or DAO, if desired).

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Student_101Author Commented:

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\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"

This is how I add something to my table in the database
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
End Function

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you would only want to work with, for example, columns named R, S or T? The simplest way would be to open a Recordset based on your Excel spreadsheet. ADO will use the first Row as FieldNames, so make sure your Excel sheet is formatted correctly, else you'll have to automate Excel to insure that you are dealing with the correct set of data.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "SELECT R, S, T FROM YourWorksheet", YourExcelConnection

Do Until rst.EOF
  YourAccessConnection "INSERT INTO Col1, Col2, Col3 VALUES(" & rst("R") & "," & rst("S") & "," & rst("T") & ")"
Student_101Author Commented:
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.
Student_101Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean by "automating the process"?

You can dynamically create your SQL statement based on what your user enters in the textboxes ... I'm not sure what format they would use for that, but if your user will enter the columns they want like this:

A, B, E, G, R

Then you would do this:

Dim sSQL As String
sSQL = "SELECT " & Me.YourTextbox.Text & " FROM YourWorksheet"
rst.Open sSQL, YourExcelConnection

Student_101Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Then you would need to automate Excel and go about it that way:

Using this example, you can see how to get to the Rows and Columns you need ...

And be forewarned - this can become quite a convoluted mess, which is why there are plenty of 3rd party import tools that do just this.
Student_101Author Commented:

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.
Student_101Author Commented:
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:

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;"""
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
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!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)
End Sub

Open in new window

Student_101Author Commented:
Are you sure that the SQL recognizes B,C,D,E as being the columns from Excel ??
Student_101Author Commented:

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...."
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
Student_101Author Commented:

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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not that I'm aware ... you'd need some sort of "mapping" table, like A=1, B=2 etc etc
Hitesh ManglaniCommented:
hi you can use the following function to obtain the column number from letters
Function ColRef(Col As String) As Integer
' Returns Excel column number
' Input:
' Col Excel column reference (eg. AA)
' Output:
' ColRef Column number (eg. 27)
'col needs to be upper case
    Col = UCase(Col)
    If Len(Col) = 1 Then
        ColRef = Asc(Col) - 64
    ElseIf Len(Col) = 2 Then
        C1 = Left$(Col, 1)
        ColRef1 = (Asc(C1) - 64) * 26
        C2 = Right$(Col, 1)
        ColRef = ColRef1 + (Asc(C2) - 64)
    End If
    If (ColRef <> 256) Then
        MsgBox "Wrong Column number", vbExclamation
        ColRef = -1
        Exit Function
    End If
End Function

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.