Open an excel from word and filter a particular column automatically [SOLVED]

[Asingham]

Hi,


I have a word document with multiple sections in it. The detailed specifications of each section is available in an excel sheet. As there are multiple sections, each section has different specifications. The excel has many columns, out of which one column indicates the section name. All the columns have filters.


Is there a way that when I'm going through the section A and would like to connect to the excel and filter the excel data with the corresponding section A in the column automatically?


Please suggest. Thanks.

 

[Asingham]

Sub recordreference()


' OpenExcel Macro

Shell ("Crogram FilesMicrosoft OfficeOffice12excel.exe ""DoctorReference List.xls""")

Worksheets("Patient").Select

ActiveSheet.Range("$A$2:$CZ$65535").AutoFilter Field:=65, Criteria1:="US"


End Sub


The above code gave a compile error message:

Sub or Function Not Defined

 

[Colin Legg]

Here's a basic starting point:

[pre]

Sub recordreference()

' OpenExcel Macro
Dim xlApp As Object
Dim xlWkb As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWkb = xlApp.Workbooks.Open("C:YourDocumentsBook5.xlsx")    'change path as necessary
xlWkb.Worksheets("Patient").Range("$A$2:$CZ$65535").AutoFilter Field:=65, Criteria1:="US"

Set xlWkb = Nothing
Set xlApp = Nothing

End Sub

[/pre]

 

[jason]

couldn't you just insert the Spreadsheet as a Embeded or Linked Object?

 

[Asingham]

The above code worked....Thanks Colin.


Jason, Yes it can be added. But, as I mentioned in my previous post, my document has multiple sections and the excel has lot of content in it. I just wanted to give the user a feel that when he clicks on the link in a particular section, he gets only relevant data.

 

[Asingham]

FYI the below link helped me how to assign a macro to text in word document.


http://word.tips.net/T001571_Assigning_a_Macro_to_a_Button_in_Your_Text.html