Solved

how to I get data from excel to be displayed in a VBA Form?

Posted on 2006-06-28
23
2,195 Views
Last Modified: 2016-08-29
I have a workbook that contains some forms.  I hope to have a couple of forms, one that I will need to pull data for viewing, one that will pull data form manipulation and another that will allow me to add data.  I have created the forms, and wrote the code to navigate through the forms.  Only problem is, I DON'T KNOW HOW TO RETRIEVE THE DATA FROM EXCEL.  If I could get an example code or two with a brief description of what the code is doing, what items I should change to match my workbook & forms, etc., so I actually learn something, I would be much appreciative.
0
Comment
Question by:neelyjer
  • 13
  • 10
23 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 17003378
A couple quick examples:
 Me.MyTextBoxName.Text = Sheets("Sheet Name").Range("A1").Value

 UserForm1.Label3.Caption = Sheets("Sheet name").Range("J10").Text

 Dim CLL As Range
 For Each CLL In Sheets("Sheet123").Range("A1:A10").Cells
  UserFormName.ListBox2.AddItem CLL.Value
 Next

If thats not what you're looking for, can you give an example of what you're trying to pull and where you want to put it?
Matt
0
 

Author Comment

by:neelyjer
ID: 17005762
Hello mvidas,


I tried the following code to display information from a different workbook and returned an error.  Perhaps you could tell me where my error is?

If "[Ticket_Status.xls]Lori".Range("F2:F129").Text = "Pending" Then
aform1.Label1.Caption = Sheets("[Ticket_Status.xls]Lori").Range("a2:e129").Text

I tried the 2nd line by itself, and it failed on its own.  I do know the format of the first line is flawed.

Thanks
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17005852
Hello,

I'm sorry, I thought you were only working in one workbook.  You could use the Workbooks collection like the sheets above to qualify it:
 If Workbooks("Ticket_Status.xls").Sheets("Lori").Range("F2:F129").Text = "Pending" Then
  aform1.Label1.Caption = Workbooks("Ticket_Status.xls").Sheets("Lori").Range("a2:e129").Text

But you're looking at multiple cells there (F2:F129, A2:E129) which can't work in the syntax above.  What are you trying to accomplish with the multiple cell range?

If you're going to be working with different workbooks/sheets a lot, it would probably be in your best interest to use Workbook and Worksheet variables.  To show you how to set them/reference them:
 Dim WB As Workbook, WS As Worksheet
 Set WB = Workbooks("Ticket_Status.xls")
 Set WS = WB.Sheets("Lori")
 If WS.Range("A1").Text = "Pending" Then
  aform1.Label1.Caption = WS.Range("B1").Text
 End If

or if you're using the same one, you can enclose it in a With block:

 With Workbooks("Ticket_Status.xls").Sheets("Lori")
  If .Range("A1").Text = "Pending" Then aform1.Label1.Caption = .Range("B1").Text
 End With

Those are just to give you an idea of different ways you can use the excel object.  If you do explain what you're looking to do, we can figure out a good way to help you.

Matt
0
 

Author Comment

by:neelyjer
ID: 17007066
What I am looking to do is this.

I have two individual workbooks.  One workbook, will contain the forms, while the other workbook will contain the actual data.  The use of the forms will allow a user to make changes to a different workbook.  the user will have the option to add new data, make specific changes to existing data, create reports, etc.  The purpose of the forms, is to keep the data isolated and keep the manipulation of the actual spreadsheet to a minimum.  I am hoping that by adding the forms and making Excel feel more like a regular Windows program, the data manipulation will be as transparent as possible and make the margin of error near non-existent.

My initial thought when starting this project o' mine, was that SQL would come into play.  I will need to pull data rows that meet more than 1 requirement.  EX:

I may need to pull  rows where the dates in column A are between this date and that, and have a status of THIS in column F, from THAT partner in column C. Only problem is I will need these multiple requirements may change. Like from my previous example, the dates most likely will need to be changed quite often, I won't always want to pull info on the same partner, and the status of that partner will most definitely change.

Any suggestions, direction or advice will be helpful.  Last thing....

I KINDA WANT TO CREATE THE CODE ON MY  OWN.  So, the most basic of code without names specific to my worksheet would better for me than anything.  If I can break down your code, and make the necessary changes, I will actually learn something as upposed to just copying and pasting your code to my project.

I have uploaded the files here:     https://filedb.experts-exchange.com/incoming/ee-stuff/295-Book1--version-1-.zip

Jeremy
0
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 17009582
Hi Jeremy,

That sounds great, I'm happy to help you learn! My favorite type of askers here are those like you who want to learn, my patience only lasts so long for those who demand I do everything for them and can't figure out how to make even the easiest changes.

I keep a few 'skeleton' subroutines around that I modify as the situation warrants.  I think it would be best for you to paste some of them here, so you can see how things can be done without them being done for you.  You can easily get data from a workbook using SQL, the best way I've found is to use the ADO object.  To give you an example of how you'd connect to a file and the different ways to use ADO to get data from an excel file (why not have an actual DB store your data?):

Sub XLAdo()
 Dim xlConn As Object 'ADODB.Connection
 Dim xlRS As Object 'ADODB.Recordset
 Dim xlSheets As Object 'ADODB.Recordset
 Dim xlFld As Object 'ADODB.Field
 Dim vFile As String
 
 'file location
 vFile = "C:\ado.xls"
 
 'connect to the file
 Set xlConn = CreateObject("ADODB.Connection")
 With xlConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Extended Properties") = "Excel 8.0;IMEX=1"
  .Open vFile
 End With
 
 'see sheet names in the immediate window
 Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
 While Not xlSheets.EOF
  Debug.Print xlSheets.Fields("TABLE_NAME").Value
  xlSheets.MoveNext
 Wend
 xlSheets.Close
 Stop
 
 'open recordset to get access to worksheet data
 strSQL = "SELECT * From [Sheet1$]"
 Set xlRS = CreateObject("ADODB.Recordset")
 xlRS.Open strSQL, xlConn, 1, 1 '"1, 1" = adOpenKeyset, adLockReadOnly
 
 'see column names in the immediate window (first row with data)
 For Each xlFld In xlRS.Fields
  Debug.Print xlFld.Name
 Next
 Stop
 
 'see cell values in the immediate window
 While Not xlRS.EOF 'starts at 2nd row of data
  For Each xlFld In xlRS.Fields
   If Not IsNull(xlFld.Value) Then
    Debug.Print xlFld.Value
   End If
  Next
  xlRS.MoveNext
 Wend
 
 xlRS.Close
 xlConn.Close
 Set xlConn = Nothing
 Set xlRS = Nothing
 Set xlFld = Nothing
 Set xlSheets = Nothing
End Sub

I usually keep everything late-binding, as it helps when distributing the files, but as you can see I've commented out what types of objects my variables actually are.

It is too bad you have to keep everything in excel.  Since you're using one file as a DB and another file for the forms, seems that second file should just be a vb exe.  There are still ways of using the userforms and manipulating the excel object to make things appear more like a regular program (take a look at http:/Q_21637947.html#15334791 to show you how you can utilize VBA by hiding the application object when the form is open and minimizing to the system tray).

I know you're still early on with your project, but something else I might suggest is to prevent users who have their macros turned off (or who hold shift during file-open to suppress the workbook_open event) from being able to do anything with the file.  I'd be happy to give you specifics on this, but in cases like this I have one "splash" worksheet explaining that macros need to be turned on.  Every time the file is saved I set the .visible property of each worksheet to xlSheetVeryHidden (after storing which sheets were open) except my splash/home sheet, saving the file, and then restoring the visible sheets to the state they were in.  That way when the file is re-opened with macros disabled, all the users can see is the one sheet.  Locking the VBA project (once completed) will prevent them from modifying the visible property themselves (though I do want to say that nothing in excel is truly secure, experienced or determined users can almost always 'break in' if they really want).

Reading through the rest of your description, without even looking at your file yet, seems like the above sub and the link there should give you a great headstart with plenty to think about and work on.  Feel free to keep this question open as long as you'd like if you have specific questions about how to do something or work with the project.

Matt
0
 

Author Comment

by:neelyjer
ID: 17009848
Matt,

One quick thing before I start modifying your code, and possibly even continue with the Excel VBA coding.  Common sense would tell me that VB and VBA would be written with the same code.  Is this true? If so, I have a copy of VB 6.0 and I'd consider discontinuing the Excel VBA coding and rebuild everything in VB 6.  What is your thought?
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17009968
VB and VBA are almost identical (or rather, are identical for most of the code), though UserForms cannot be directly imported into vb (among other minor annoyances).  The only difference between them a lot of the code itself is that you'd have to qualify the methods used with the excel application object.  For example, instead of simply using
 Range("A1")
You'd have to use
 xlApp.Range("A1")

or
 Workbooks("Filename.xls")
becomes
 xlApp.Workbooks("Filename.xls")

etc.  That is mostly all that would prevent a direct copy/paste from one into the other.  Though from the sound of it, you probably wouldn't necessarily even need to use the excel object, if you'll be accessing the file via ADO (be careful of people using the excel file while you're accessing it though).

Also, for what it is worth, the code xlADO code code above can be run directly from VB, as nothing in there actually references excel.  The link does, but that link is really to get the excel form to look/act like a regular VB form :)  I'd say go for it, especially if you have VB already.  Depends what the end-users (or their superiors, rather) think about it.
Matt
0
 

Author Comment

by:neelyjer
ID: 17010300
    Regarding superiors, you are absolutely correct.  I think you'll appreciate the history of this project so I'm going to share it with you whether you like it or not.  I am currently working for the IT dept. at Safeway Inc. , a grocery store chain located mostly on the West Coast US, working directly with the software that controls ALL of our electronic transactions.  

     I have expressed to my superiors the desire to learn how to program.  This excel project started out as simply taking a spreadsheet full of seemingly random info and performing some minor calculations.  When I completed that task and turned the calculated info over to my boss, he said that he wanted to view this information specific to a partner.   So I created another workbook, not included in the .zip I uploaded, that pulled specific data pertaining to a partner and added the ability to view this information within a specific date range, by typing a start date & end date into two individual cells.  After turning this over to my boss, he told me he didn't want to have to type in a date range, instead he wanted to click on 'something', as he put it.  So I had to go out and research and figure out how to use the calendar object of Excel.  After getting that working, I turned it into my boss again.  This time my boss gave me a whole list of things he wanted to be able to do.  In my original workbook, I had employee names and partners hardcoded into everything.  Now he want's me to include the ability to add/remove users as necessary, & add/remove partners as necessary.  Also, he wanted me to make the workbook as user friendly as possible.  He told me to think of my dumba$$ neighbor down the street, that knows nothing about computers or using them.  If I were to give the spreadsheet to this guy, would he be able to use it?  I thought to myself, probably not.  So I turned to user forms.  And it now looks af if I will be using VB 6.0 instead.

I think it's cool to see the progression of this workbook over the last 6 weeks.  To go from having enough knowledge of Excel to stay afloat, to creating simple formulas, to creating more involed formula arrays, to coding for a calendar object, to figuring out and creating the code to navigate the forms I created in Excel, to considering droping the Excel user forms altogether and use VB 6.  My boss is a genious!!!  I feel like the karate kid! Starting out washing cars and painting fences, to kicking some serious butt.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17010694
Great progression! Similar to mine, though I decided to learn to really start programming in excel out of curiosity more or less, but the learning curve sounds about the same.  Its amazing how much you can learn so quickly, and theres nothing like writing something from the ground up and have it work exactly as you envisioned.

One thing to keep in mind about bosses or people who don't do much with computers, they seem to really love excel.  Might be worth keeping it all in there to get some extra brownie points, plus it will give you a chance to hone your skills a little more.  Because of this love for excel, there will almost always be projects given to you (I'm sure) once you prove yourself with a project like this.  
Another thing most people seem to love are dropdowns (comboboxes/listboxes).  There are a number of ways to populate these based on the information in a worksheet (I gave a basic example in my first comment above, looping through all the cells in a range), even when you don't know the exact size of the list.  Give the boss a space in the sheet to list the names and partners, and you can dynamically fill the box with the data.  A quick example, lets say the list of partners is in book "Filename.xls" on sheet "Setup" column B starting in cell B2:

Private Sub UserForm_Initialize()
 Dim RG As Range
 With Workbooks("Filename.xls").Sheets("setup")
  Set RG = .Range("B2", .Cells(.Rows.Count, 2).End(xlUp)) 'set range to used cells in B
  Me.ComboBox1.List = RG.Value 'put those partner names into the combobox
 End With
End Sub

That way you only have to know the initial cell of the list, and it can be dynamically created.  If you have a few dropdowns, you can even create a function that you send just the control and the column for each one.  All this can still be done via VB, so don't let that influence your decision about which way to go.
0
 

Author Comment

by:neelyjer
ID: 17011803
Matt,

So I have a better understanding. In your code:

Sub XLAdo()
 Dim xlConn As Object 'ADODB.Connection
 Dim xlRS As Object 'ADODB.Recordset
...

What does the term 'Dim' mean and/or do?  Is it calling a function of Excel?

And

Set xlConn = CreateObject("ADODB.Connection")

The term 'Set', it looks like you're creating a reference that will be used later in the code.  Is my assumption correct?
0
 

Author Comment

by:neelyjer
ID: 17011833
Also,

 'file location
 vFile = "C:\ado.xls"

Would this line be the location of the workbook I'll be pulling data from ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 35

Expert Comment

by:mvidas
ID: 17011961
>>What does the term 'Dim' mean and/or do?  Is it calling a function of Excel?

'Dim' is short for "Dimension", which is used to declare a variable and set memory aside for it.  It isn't specifically used in VBA, it is a standard VB statement. Something that may help, put your cursor so it is touching the word Dim, and press F1 to bring up the associated help topic for that.


>>The term 'Set', it looks like you're creating a reference that will be used later in the code.  Is my assumption correct?

Yes, in part.  When you're assigning something to an object variable (like a Workbook object, Worksheet object, Range object, or the ADODB objects above) you use Set to assign a reference to that object, since "xlConn" wouldnt actually be the connection or store the connection.. this allows you to directly use that object's methods/properties.


>>Would this line be the location of the workbook I'll be pulling data from ?

Yep.  It is useful to have a string variable hold something like that, so you can call it from different parts of the procedure without having to write the string out each time.  Makes it much easier to change it once than change it multiple times.  In my above code I'm only referencing it once (when opening a connection), but it also helps to have everything that needs to be set like that in one location.
0
 

Author Comment

by:neelyjer
ID: 17012780
Matt,

I'm a bit confused.  Where am I placing this code?  I would like for the above actions to take place with CommandButton1_click(). CommandButton1 is use with its own private sub, making it impossible to paste the above code into Private Sub CommandButton1_click().  What am I missing here?
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17013021
I'm not sure I understand what you're asking, Jeremy... if you want to put that ADO code into commandbutton1_click, just copy/paste it into it (not the Sub XLAdo() or End Sub lines -- you would only include those if you were pasting the XLAdo sub in it's entirety, and calling it from commandbutton1_click, like
Private Sub CommandButton1_Click()
 XLAdo
End Sub
0
 

Author Comment

by:neelyjer
ID: 17013061
You have answered my question.  I will try to work out any errors that I will come across before bugging you anymore.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17013091
OK, that is up to you. Stumbling through it and trying to get it to work is usually a great way to learn so I won't stop you; just let me know if you have any more questions.
0
 

Author Comment

by:neelyjer
ID: 17014535
Okay,  I've tried slightly altering the code here and there and this is what I've come up with.  After only changing the sheet name at line 28 of your code, I receive an error that highlights stop at line 25 of your code.  I've tried changing the ("Table_Name") portion of your code.  I don't know what that line is trying to do, but by the looks of the code, I assume it is trying to display some data.  If I change the "Table_Name" I receive the error, 'Run-time error 3265', telling me that the code should be as you have it.  If I knew why on line 25, stop is highlighted, maybe I could figure it out.  From what I can tell, the sheet name would be the only thing I would need to change.

Any ideas????
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17014603
Is there an error on the line "Stop", or does the code stop there? That is a vb keyword I used to pause the code from running so you can see the sheet names listed.  Putting "table_name" in there is used to display the sheet names in the Immediate/debug window.  If that isn't open on your screen, press control-g while in the VB editor. The lines that begin with "Debug.Print" will display what comes afterward in the immediate window. I included those lines to show you how to get the sheet names, which can be very usefulif you didn't know the exact name but could match a pattern.  

When the code reaches the word "Stop" you can either continue the code by pressing F5 until it reaches the next Stop line, or press F8 to step through the code one line at a time.  Stepping through can be great so you can see what values the variable holds during runtime.

The part in the code you would change to match a sheet name would be the SQL statement line:
 strSQL = "SELECT * From [Sheet1$]"

If your sheet name has a space in it, like "Sheet 1", you would enclose that in apostrophes, like:
 strSQL = "SELECT * From ['Sheet 1$']"

That may have been your error, if not let me know and I'll take a look tomorrow.
0
 

Author Comment

by:neelyjer
ID: 17015461
Well, while viewing the debug window & running the command I can see sata being pulled from the referenced woorkbook & sheet.  The code seems to work if I am pressing F8 to tell it to continue.  I only say it seems to work, because I would think the code should just run from begining to end without interuption.  I did not mean to sound degrading by saying it seems to work.  Believe me, you've expanded my scope of excel VBA with uour help, the last thing I want to do is pi$ you off.  

Anyway, now I just need to figure out how that data is to be displayed on the form.  Where we used ' * ' to pull ALL the data from the worksheet I noticed in the debug window that only the last few lines of the worksheet were viewable.  Is there a line limit on the debug window?  Can that limit be changed?
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17018677
Hi Jeremy,

It take a lot to upset me, so I wouldn't worry about that.  I think I should have just explained more earlier, I didn't realize this was really your first major venture into VB/VBA.  I wish I had the time today to teach more, but hopefully I can at least guide you somewhat with the little time i do have.

The reason the code stops is because of the lines that say:
 Stop

This was purely intentional, as the code I gave you was really just for informational purposes or how the connection works, and not to be used in your project directly.  If you want the code to run non-stop, remove the lines that say "Stop".  If you want the information returned somewhere besides the immediate window, just send it there instead.  The immediate window should have scroll bars, I think the line limit is very large (but is unchangeable).  You wouldn't necessarily want all that information on your form anyways, I just wanted to show you how you could access specific pieces of data (the most common) and the syntax to do so.
I do strongly recommend you use the VB help (press F1 when the cursor is touching the statement you're wondering about).  It has taught me more than every other resource.
As I said, I have limited time today and probably will not have any time until next week, likely not until wednesday (after our independence day holiday tuesday--I'm guessing by your posting times that you're probably not in the USA).
Matt
0
 

Author Comment

by:neelyjer
ID: 17051574
Hey Matt,

Trying not to be bothersome here.  I have ran the code through the debug as you suggested.  The information from the spreadsheet is scrolling through the debug window, confirming that the code works and is retrieving information from the worksheet it is connecting to.  However, when I run the code out of debug, nothing is showing on the form.  Obviously I am missing something here.  If I am correctly analyzing what the different lines of code are doing, it looks like the lines begining with     Debug.Print ...     are what tells the data where to be displayed.  Is that correct?  I have tried changing the "Debug" portion of the code to the name of the sheet and to the name of a label on the sheet, but with no success.

Jeremy
0
 

Author Comment

by:neelyjer
ID: 17075304
Mvidas, are you still there?
0
 

Author Comment

by:neelyjer
ID: 17085905
Mvidas,

Excellent help, although I still haven't figured out how to get the data to show anywhere else than a debug window.  I have posted a new question in continuation of this one.  Attempting to get answer of how to get the data pulled from excel to display in a already created form.

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21915840.html
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now