Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

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.
0
swambast
Asked:
swambast
  • 9
  • 5
1 Solution
 
GrahamSkanCommented:
Do you have somewhere that you can post the docs?
0
 
GrahamSkanCommented:
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
0
 
swambastAuthor Commented:
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"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GrahamSkanCommented:
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.



0
 
GrahamSkanCommented:
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
0
 
swambastAuthor Commented:
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?
0
 
GrahamSkanCommented:
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?
0
 
GrahamSkanCommented:
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
0
 
swambastAuthor Commented:
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
0
 
GrahamSkanCommented:
Hi. Sorry to have been so long.
Your new version does't work too well on my system. It falls over on the SortColumn in the spreadsheet.

Here is the code for the filename

Option Explicit
Dim SelectingFilename As Boolean ' Flag to prevent race condition

Private Sub cmbDept_Change() 'This call needs to be in all the other four combos
    NewFileName
End Sub

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

Sub NewFileName()
If Not SelectingFilename Then
    tbProposedFN.Text = cmbProjectName2.Text & "-" & CmbAuthor.Text & "-" & _
    cmbDept.Text & "-" & cmbDate.Text & "-" & cmbILM.Text
End If
End Sub

 
I'm still a bit confused about where the sort is needed.
0
 
swambastAuthor Commented:
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!!!
0
 
GrahamSkanCommented:
Thanks for taking the effort to keep me informed. Hope your other project gets sorted soon.
0
 
swambastAuthor Commented:
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.
0
 
GrahamSkanCommented:
Thanks swambast and have a Happy New Year.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now