access excel worksheet from word

[WalkerEvans]

I have written a macro to capture data from a Word-based form and transfer it to an Excel spreadsheet. I can capture the data and write it, no problem, but I need to write to a specific worksheet, depending on the data in one of the captured Word form-fields. I can't find a command combination that will do this; either everything goes to the first worksheet, or I get an error.

Does anyone know of way to write a line of VBA code that will shift focus to a specific worksheet within a workbook?

 

[PHV]

Any chance you could post the VBA code where you're stuck ?
A starting point:
myXLapp.Workbooks("mySpreadSheet").Worksheets("mySheet").Range("C7") = myValue

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WalkerEvans]

Actually, its on another computer, but here is where the problem lies (this is off the top of my head - hopefully I'm not messing up too badly):

Set ExcelSheet = GetObject ("u:\Voucher Test.xls")
ExcelSheet.Visible = True
ExcelSheet.Parent(1).Visible = True
[Here is where I have been trying to set the focus to a specific worksheet, based on a previously captured data element set into a variable)
ExcelSheet.Application.Cells(1,2) = Name1
ExcelSheet.Application.Cells(1,3) = Emp
(etc)

As noted, I am capturing the data, assigning it to dimensioned variables, opening the proper workbook, and can write it - but only to the first sheet. I can't find any VBA elements that will allow me to pick which worksheet the data needs to post to. (And since this is not my pidgeon, I can't do the obvious and reformat the workbook.)

Thanks!

 

[PHV]

You wanted this ?
ExcelSheet.Application.ActiveWorkbook.Worksheets("specific").Cells(1,2) = Name1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WalkerEvans]

PH,

I'm terribly sorry that it has taken me so long to get back to you on this; my disabled son went into crisis and things got complicated. Your solution worked perfectly and the folks in my office who wanted the macro are very satisfied.

(This all would have been SO much easier if the project had been mine and/or they would have let me make the changes that I knew would solve their problems.)