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.
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.
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
http://plus.xdrive.com/u/71821986/4561235818CmRASHV6bKHsxpzIEt
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"
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.
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 \GMACProje ctList.xls "
ExcelFile = "C:\GMACProjectList.xls"
'FOR REAL TIME ENVIRONMENT
'ExcelFile = "S:\GMACProjectList.xls"
'Documents.Add ' create a new document
Set xlApp = CreateObject("Excel.Applic ation")
xlApp.Visible = True 'show while developing
'xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open(Excel File)
' open an existing workbook
' example excel operations
FillCombo frmFileSaveAs.cmbProjectNa me, 1, 1
FillCombo frmFileSaveAs.cmbProjectNa me2, 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
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\
ExcelFile = "C:\GMACProjectList.xls"
'FOR REAL TIME ENVIRONMENT
'ExcelFile = "S:\GMACProjectList.xls"
'Documents.Add ' create a new document
Set xlApp = CreateObject("Excel.Applic
xlApp.Visible = True 'show while developing
'xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open(Excel
' open an existing workbook
' example excel operations
FillCombo frmFileSaveAs.cmbProjectNa
FillCombo frmFileSaveAs.cmbProjectNa
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
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
ASKER
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?
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?
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 \GMACProje ctList.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.Applic ation")
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(Excel File)
End If
' open an existing workbook
' example excel operations
FillCombo frmFileSaveAs.cmbProjectNa me, 1, 1
FillCombo frmFileSaveAs.cmbProjectNa me2, 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
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\
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.Applic
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(Excel
End If
' open an existing workbook
' example excel operations
FillCombo frmFileSaveAs.cmbProjectNa
FillCombo frmFileSaveAs.cmbProjectNa
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
ASKER
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.