I really need some help. I have been searching for a solution for almost a month now (not constantly) but I couldn't find a way to make it work.
First of all this is the code I have to open the Workbook, which works perfectly well:
Private Sub OpenExcelDatabase()
Dim oxl As Object
Set oxl = CreateObject("Excel.Application")
Dim xlPath As String
xlPath = "OSX:Users:" & GetUserName.GetUserName & ":Documents:Rechnungen:"
Dim xlFile As String
xlFile = "Database_Rechnungen.xlsm"
oxl.Workbooks.Open (xlPath & xlFile)
Application.ActiveWindow = ActiveDocument
Dim xlWB As Object
Set xlWB = oxl.Workbooks.Open(xlPath & xlFile)
xlWB.Application.Run "connectSQL"
End Sub
Now I need to get some data from that Workbook for 2 different Userforms which are located in the new word document. In the VBA-menu I have already activated the Object-Library for Excel but the problem is that even though it seems to know the functions (it shows all the options that I would need), it can't run them. The thing is that I need to run a Macro which is located in the Workbook as soon as a commandbutton from a Userform in Word is pressed.
Maybe to clarify things:
I have a word-template which is used to write invoices. There are 2 (important) Userforms - SearchCompany and SearchPerson. In these two UserForms the user can enter a string and this string shall be searched in the excel-database-cells. If this string is existing in one of the searched cells, it shall return all the information of that company back to the UserForm. The same with the person. In my Workbook I have a sheet with the companies and one sheet with the people that work in the companies.
These are the codes I have already tried to refer to the opened Workbook or to run a macro in that Workbook:
1. xlWB.Application.Run "SearchFirma"
2. Excel.Application.ActiveWorkbook.Application.Run "SearchFirma"
3. Excel.Workbooks("Database_Rechnungen.xlsm").Application.Run "SearchFirma"
4. Set xlWB = GetObject("Database_Rechnungen.xlsm")
5. Set xlWB = oxl.Workbooks("Database_Rechnungen.xlsm")
6. Set xlWB = Excel.Workbooks(1)
If I used the workbooks.Open-Method once again I get the error-message, that this workbook is already opened... The problem is that to get the information back to word I would also need to refer to an opened, yet not named or saved word-document ("document1").
Whatever I tried didn't work. I have searched half google for a solution, but no one has ever had such a problem... As soon as most people activated the object libraries, everything worked. Why doesn't it work with me? Is there anything else I need to check? (Object-library is existing) The function getObject ain't working on mac, but maybe someone knows a workaround? I would be unbelieveably thankful for everyone who tries to help me with this, as I am starting to get really desperate for finding a solution to this problem.
I found a way to do it:
Private Sub Document_New()
xlPath = "OSX:Users:" & GetUserName.GetUserName & ":Documents:Rechnungen:"
xlFile = "Database_Rechnungen.xlsm"
Call OpenExcFileWithApp(xlPath, xlFile, "connectSQL")
UFFirmaSearch.Show
End Sub
And this is the function:
Public oxl As Object
Public xlWB As Object
Public xlPath As String
Public xlFile As String
Public FSearchText As String
Public FResult As ListBox
Function OpenExcFileWithApp(Path As String, File As String, RunMacro As String)
Dim FileFullName As String
FileFullName = Path & File
Set oxl = CreateObject("Excel.Application")
Set xlWB = oxl.Workbooks.Open(FileFullName)
If RunMacro <> "" Then
xlWB.Application.Run RunMacro
End If
End Function
With this everytime I use xlWB in my word-Modules it refers to the opened Workbook. :)
Thank you Kiroshiro, as you brought me upon the idea :)