Link to home
Start Free TrialLog in
Avatar of swambast
swambast

asked on

VBA - Using MS Word VBA to obtain Excel data

I think VB forum would be best for this question, but not entirely sure...

I have a user input form created in Word VBA.  I would like Word to open an external excel spreadsheet (already coded in VBA and working fine).  Then, it will read all the cell values from a certain column that are not empty/null.  There are several columns such as Project Name, Department, Date, etc.  After this, it takes those cell values and sorts them alphabetically into an array for each column.  I can then use the contents of each sorted array to assign it to a variety of drop down combo boxes on my Word form.

I would be happy to offer a jump start on this solution by providing the actual Word and Excel documents for testing purposes, if desired.  Thank you for your consideration in helping me with this challenge, I appreciate it.
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Do you have somewhere that you can post the docs?
If not you can post them here, though you will have to register with the x-drive people
http://plus.xdrive.com/u/71821986/4561235818CmRASHV6bKHsxpzIEt
Avatar of swambast
swambast

ASKER

OK, I created a quick stash spot for the files, try here:  http://home.twmi.rr.com/portalaccess/
The files you want are "GMACProjectList.xls" and "Access Excel Workbook.doc"
To explain to others,

Your word document is virtually blank except explanatory information to the developer.

You have a UserForm with some combo boxes, a text box and a command button. There is no code.

There is a code module with outline code to open the workbook and to read the first column, copying each cell's contents to a line on the Word document, until a blank cell is encountered. There is an unused procedure implementing the quicksort algorithm.

The workbook comprises two sheets. The first has 8 columns with some sample data. The first column is labelled project name whose data, I believe, need loading into a combo on the Word user form. Column H (No 8) has a file name for the project.

There is a TextBox a Picture of the Word UserForm to explain what is required.

The second sheet has columns with data for other combo boxes on the Word UserForm.



Hi,
I've modified your code a bit.

This is the module code:

Option Explicit
Public xlApp As Excel.Application
Public xlWB As Excel.Workbook

Sub OpenAndReadExcelWB()
'Choose Tools...References and then add-in Microsoft Excel 10.0 Object Library
Dim tString As String, r As Long
Dim ExcelFile As String

'FOR TESTING ENVIRONMENT
'ExcelFile = "C:\Documents and Settings\Graham Skan\My Documents\Allwork\Experts\Q_21210453\GMACProjectList.xls"
ExcelFile = "C:\GMACProjectList.xls"

'FOR REAL TIME ENVIRONMENT
'ExcelFile = "S:\GMACProjectList.xls"

    'Documents.Add ' create a new document
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True 'show while developing
    'xlApp.ScreenUpdating = False
    Set xlWB = xlApp.Workbooks.Open(ExcelFile)
    ' open an existing workbook
    ' example excel operations
    FillCombo frmFileSaveAs.cmbProjectName, 1, 1
    FillCombo frmFileSaveAs.cmbProjectName2, 1, 1
    FillCombo frmFileSaveAs.cmbDept, 2, 1
    FillCombo frmFileSaveAs.cmbDate, 2, 2
    FillCombo frmFileSaveAs.cmbILM, 2, 3
    FillCombo frmFileSaveAs.CmbAuthor, 2, 4
    frmFileSaveAs.Show 1
End Sub

Sub FillCombo(Cbo As ComboBox, iSheet As Integer, iColumn As Integer)
Dim r As Integer
    r = 2
    With xlWB.Worksheets(iSheet)
        Do Until .Cells(r, iColumn).Value = ""
            Cbo.AddItem .Cells(r, iColumn).Value
            r = r + 1
        Loop
    End With
End Sub

This is the form code:

Option Explicit

Private Sub cmbProjectName_Change()
Dim i As Integer
For i = 0 To cmbProjectName.ListCount - 1
    If cmbProjectName.Text = cmbProjectName.List(i) Then
        Me.tbProposedFN.Text = xlWB.Worksheets(1).Cells(i + 2, 8)
        Exit For
    End If
Next i
End Sub


Private Sub UserForm_Deactivate()
    xlWB.Close False ' close the workbook without saving
    xlApp.Quit ' close the Excel application
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub
GrahamSkan

Very nice and impressive start, I sincerely thank you!  

One of the things I'd like to do though, is assign the values taken from each of the spreadsheet columns into its own alphabetically sorted array/variable.  Why?

Well, let's say on the form that I wanted to change the author name.  Here is what should happen:  I click the drop-down combo box called "Author Name."  An alphabetically sorted list of author's names appear.  Then, once the desired author's name is clicked on from the combo box, the proposed file name textbox (tbProposedFN) automatically updates to reflect this change.  Might you consider assisting with that?
Yes,

Unfortunately the combo box does not have a sorted property as the combo in standalone VB does.

That leaves three options.
1. Search for the correct insertion point before adding each item.
2. Use a sort procedure, such as the one supplied
3. Use Excel to sort the data. This is my preferred solution, but it is only possible if the columns of data on the lookup sheet are independent of each other.

What do you think?
This uses the Excel.sort method to sort the columns individually.

Note that the code now only the workbook if it is not already open.

The 'date' field is not sorted. I don't suppose that you want it in alphabetical order - the format is not recognised as a date in VB.

Option Explicit
Public xlApp As Excel.Application
Public xlWB As Excel.Workbook

Sub OpenAndReadExcelWB()
'Choose Tools...References and then add-in Microsoft Excel 10.0 Object Library
Dim tString As String, r As Long
Dim ExcelFile As String

'FOR TESTING ENVIRONMENT
'ExcelFile = "C:\Documents and Settings\Graham Skan\My Documents\Allwork\Experts\Q_21210453\GMACProjectList.xls"
ExcelFile = "C:\GMACProjectList.xls"

'FOR REAL TIME ENVIRONMENT
'ExcelFile = "S:\GMACProjectList.xls"

    'Documents.Add ' create a new document
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    xlApp.Visible = True 'show while developing
    'xlApp.ScreenUpdating = False
    For Each xlWB In xlApp.Workbooks
        If xlWB.FullName = ExcelFile Then
            Exit For
        End If
    Next xlWB
    If xlWB Is Nothing Then
        Set xlWB = xlApp.Workbooks.Open(ExcelFile)
    End If
    ' open an existing workbook
    ' example excel operations
    FillCombo frmFileSaveAs.cmbProjectName, 1, 1
    FillCombo frmFileSaveAs.cmbProjectName2, 1, 1
    FillComboSort frmFileSaveAs.cmbDept, 2, 1
    FillCombo frmFileSaveAs.cmbDate, 2, 2
    FillComboSort frmFileSaveAs.cmbILM, 2, 3
    FillComboSort frmFileSaveAs.CmbAuthor, 2, 4
    frmFileSaveAs.Show 1
End Sub
Sub FillCombo(Cbo As ComboBox, iSheet As Integer, iColumn As Integer)
Dim r As Integer
    r = 2
    With xlWB.Worksheets(iSheet)
        Do Until .Cells(r, iColumn).Value = ""
            Cbo.AddItem .Cells(r, iColumn).Value
            r = r + 1
        Loop
    End With
End Sub

Sub FillComboSort(Cbo As ComboBox, iSheet As Integer, iColumn As Integer)
Dim r As Integer
Dim iMax As Integer
    r = 2
   With xlWB.Worksheets(iSheet)
  .Range(Chr$(Asc("A") + iColumn - 1) & "1").Sort _
    Key1:=.Columns(iColumn), _
    Header:=xlYes
        Do Until .Cells(r, iColumn).Value = ""
            Cbo.AddItem .Cells(r, iColumn).Value
            r = r + 1
        Loop
    End With
End Sub
GrahamSkan, I am sorry for the delay, but it was Friday and we went into the weekend where I was tied up.  I really apologize, but I already had the sorting completely worked out.  In fact, it is working better than expected.  I have updated both files again, and you can find them in the same place:  http://home.twmi.rr.com/portalaccess/

GrahamSkan, I'm not trying to be stingy or difficult to work with here, I'm sorry if I'm coming across like that.  Obviously you are going to be awarded these points, soon.  But I guess I am frustrated because my original problem still remains!  That is why I wanted to somehow have an array/variable solution as I posted in the first place.  The problem really still is this: "Well, let's say on the form that I wanted to change the author name.  Here is what should happen:  I click the drop-down combo box called "Author Name."  An alphabetically sorted list of author's names appear.  Then, once the desired author's name is clicked on from the combo box, the proposed file name textbox (tbProposedFN) automatically updates to reflect this change."  

I wanted the array/variable solution to help with this.  For example, the full file name might be "GMPP-CBruce-GMACI-Nov2004-HUM0009" stored in the the proposed file name textbox (tbProposedFN).  But now, unless I somehow have a variable to hold the contents of the combo box, I don't know a way that this textbox can be updated!!!!  So, if I use the combo drop down to change the author to ASmith, the new proposed file name textbox (tbProposedFN) should read "GMPP-ASmith-GMACI-Nov2004-HUM0009."  Furthermore, ideally the "change file name" combo boxes should default to what the file name currrently is:  i.e., the author combo box first defaults with "CBruce", the date defaults to "Nov2004", etc.  

I just feel that my original struggle for posting this request still remains.  I'm sorry if you feel like I'm being a pain, I certainly appreciate all your help and insight.  What are your thoughts...  

Steve
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
GrahamSkan, my sincerest apologies for the long delay.  I have shifted into crisis mode on another project, but I know I still owe you some points soon!  I will try to test out your solution/code this weekend, and if all looks well, award the points.  Thanks again for all your help, and your patience!!!
Thanks for taking the effort to keep me informed. Hope your other project gets sorted soon.
GrahamSkan, again I apologize for the delay.  I went over the spreadsheet, and as you know, my sense of urgency on this project has changed.  There are still serveral VBA errors that occur (i.e., when you go to change each field entry details, and then go back to the top of the form and change the main drop down box, an error is generated).  However, you have more than stayed the course with me here, and I am awarding you these points as you rightfully deserve.  Hope you had a wonderful holiday, and again, I appreciate all your help.
Thanks swambast and have a Happy New Year.