• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1097
  • Last Modified:

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.
0
Student_101
Asked:
Student_101
  • 10
  • 7
5 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you are automating an Access database from within a Visual Basic application?
0
 
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.
0
 
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:

http://support.microsoft.com/kb/257819

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).
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

?


''' 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

Open in new window

0
 
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") & ")"
  rst.MoveNext
Loop
0
 
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.
0
 
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.
0
 
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

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

http://support.microsoft.com/kb/219151

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

Open in new window

0
 
Student_101Author Commented:
Are you sure that the SQL recognizes B,C,D,E as being the columns from Excel ??
0
 
Student_101Author Commented:
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...."
0
 
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).
0
 
Student_101Author Commented:
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.
0
 
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
0
 
hiteshgoldeneyeCommented:
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

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now