Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Populate Excel 2007 ListBox with TSQL Query Result Set

Posted on 2013-01-14
2
Medium Priority
?
504 Views
Last Modified: 2013-01-14
I have an Excel worksheet named "Single-Column" that has a Form Control List box which I want to populate with the result set (single column) from a TSQL Query against a SQL Server 2008 R2 database table column using ADO.  I've placed the attached code in a module, but am getting a compile error when I try to reference Sheet1.Name, which is valid syntax.

Attached is the code that I am working with.  What am I doing wrong?

Thanks,

WW
PopLstBxFrmSQLSvr.txt
0
Comment
Question by:usernameX
2 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38776650
You can't use a With statement to execute code.

If you want to rename the worksheet try this.
With Sheet1
    .Name = "Single-Column"
    With .ListBox158
        .Clear
        .BoundColumn = k
        .List = Application.Transpose(vaData)
        .ListIndex = -1
    End With
End With

Open in new window

That should fix that problem, but I think you might have a problem on the next line.

It won't work if the listbox is a Forms listbox, it would work for an ActiveX listbox though.
0
 

Author Closing Comment

by:usernameX
ID: 38776758
Duh!,

Thanks.

WW
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question