I need to write a MSWord macro that searches an Excel spreadsheet and shows the spreadsheet with the cell selected.
We start with an Excel sheet listing all the patients to be seen on a daily basis.
Once the patients are seen by a provider, a note is generated using MSWord and printed. After the note prints, a notation next to the patient’s name in the Excel sheet should be made for statistical purposes.
The patient’s names are on Column C in the Excel sheet and I want to have the Word macro open the spreadsheet (and this is done already with the code I show you below), then find the patient’s name in Column C and wait for the provider to make a notation on the next cell that the patient was seen..
The variable PatientName$ is public variable automatically generated by a “printing macro”. For my LittleTest () purpose I created an input box to generate the variable until I solve the problem.
Please look where it says “This is where I have the problem and NEED HELP.” Thank you.
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
Sub LittleTest()
' This is a test to check if the name will be found in Excel..
' Create the variable that will be tested. This should be found in the DSL sheet
PatientName$ = InputBox("Please enter a patient's Name", "Patient Identity"
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application"
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel
' Set the object variable to reference the file you want to see.
Set MyXL = GetObject("L:\shared\pa\MyDirectory\MySpreadSheet.xls"
' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Do manipulations of My file here.
' This is where I have the problem and NEED HELP
Set Cell = Excel.Cells
Range(Cells(1, 3), Cells(1000, 3)) = True
Excel.Application.Cells.Find(what:=PatientName$, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
' any help to correct the above statement so it works will be appreciated. Thank you.
Set MyXL = Nothing ' Release reference to the
' application and spreadsheet
GoTo Quit
Quit:
End Sub
We start with an Excel sheet listing all the patients to be seen on a daily basis.
Once the patients are seen by a provider, a note is generated using MSWord and printed. After the note prints, a notation next to the patient’s name in the Excel sheet should be made for statistical purposes.
The patient’s names are on Column C in the Excel sheet and I want to have the Word macro open the spreadsheet (and this is done already with the code I show you below), then find the patient’s name in Column C and wait for the provider to make a notation on the next cell that the patient was seen..
The variable PatientName$ is public variable automatically generated by a “printing macro”. For my LittleTest () purpose I created an input box to generate the variable until I solve the problem.
Please look where it says “This is where I have the problem and NEED HELP.” Thank you.
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
Sub LittleTest()
' This is a test to check if the name will be found in Excel..
' Create the variable that will be tested. This should be found in the DSL sheet
PatientName$ = InputBox("Please enter a patient's Name", "Patient Identity"
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.
' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application"
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel
' Set the object variable to reference the file you want to see.
Set MyXL = GetObject("L:\shared\pa\MyDirectory\MySpreadSheet.xls"
' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Do manipulations of My file here.
' This is where I have the problem and NEED HELP
Set Cell = Excel.Cells
Range(Cells(1, 3), Cells(1000, 3)) = True
Excel.Application.Cells.Find(what:=PatientName$, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
' any help to correct the above statement so it works will be appreciated. Thank you.
Set MyXL = Nothing ' Release reference to the
' application and spreadsheet
GoTo Quit
Quit:
End Sub
