August 29, 2008 08:06pm pdt

Member Login

Hall of Fame

Yearly

1TheLearn... 199,259
2jaime_oliv... 177,837
3jpaulino 133,845
4Dhaest 130,766
5Idle_Mind 109,194
6Fernando... 104,465
7angelIII 97,115
8emoreau 91,478
9ZachSmith 89,365
10PaulHews 88,152
11Sancler 82,419
12graye 82,050
13jcoehoorn 68,170
14naspinski 59,130
15webtubbs 57,243
16vb_jonas 55,375
17VBRocks 54,125
18Mikal613 53,025
19GreenGho... 51,947
20rachitkohli 44,214
21JimBrandley 41,514
22nsanga 41,340
23raterus 40,750
24surajguptha 40,350
25jandromeda 39,640

	[x] Attachment Details

Select only columns with data from Excel spreadsheet

Zones: .NET, Microsoft Access Database, Microsoft Excel Spreadsheet Software

Tags: Microsoft, Access, Access 2000

Experts,

The code snippet below works great to import an Excel worksheet into an Access table.

However, it imports all columns of the worksheet. I just need the columns with data.

Is there a way to frame a condition (WHERE ...) so that only columns containing data are selected?

Capt

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

           
           
             Private Sub GetExcelData(ByVal file As String, ByVal sheet As String)
        Try
            ' pre-init progress bar ...
            pbrData.Minimum = 0
            pbrData.Maximum = 100
            ' clear the table ...
            dt.Clear()
            ' HDR = header (set = no, if no header row) ...
            Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & file _
                & "; Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
            Dim xlCn As OleDb.OleDbConnection = New OleDb.OleDbConnection
            xlCn = MakeCnn(xlCn, strConn)
            xlCn.Open()
            ' delineate the sheet name with a $ after it ...
            ' select data [optional cell range just after sheet name] ...' eg [April$A1:AD83]"
            ' select columns (F1, F2 ...)
            Dim xlSql As String = "SELECT * FROM [" & sheet & "$]"
            Dim xlDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(xlSql, xlCn)
            xlDA.Fill(dt)
            ' init progress bar ...
            pbrData.Minimum = 0
            pbrData.Maximum = dt.Rows.Count
            ' send to grid ...
            grdCalls.DataSource = dt
        Catch x As OleDbException
            Alert(x.Message, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

           
         

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.4

Question Stats

Zone: Programming

Question Asked By: capt_alberta

Solution Provided By: jppinto

Participating Experts: 1

Solution Grade: B

Translate:

Loading Advertisement...

20080723-EE-VQP-34 / EE_QW_2_20070628