PLEASE!

Wingz · Apr 10th, 2002, 01:42 PM

Can someone PLEASE tell me how to make reference to a cell in a closed workbook WITHOUT using the R1C1 reference format. I need to use Cells(x,y)so I can loop.

It may just be a syntax thing but here is what I want:
activecell.value = Workbooks("C:My Documentsemployee.xls).Worksheets(1).cell(x,y).value ----> Or something to this effect. I know the syntax is off but if ANYONE can help, I would greatly appreciate it.

AJ · Apr 10th, 2002, 02:05 PM

Ok, here goes.

I've had a look at this and your earlier post and I'm not sure why this has to be in a macro but I'm sure you have your reasons.

First things first, the full formula that needs to go in cell A1 of the active workbook is:
='C:My Documents[Employees.xls]Sheet1'!$B1&" "&'C:My Documents[Employees.xls]Sheet1'!$C1

As there is no dollar($) in front of the row reference, you could just copy and paste this formula down.

If you wanted a macro to go through and fill each in for 75 rows then how about:

Sub test ()

For x = 1 To 75
Range("A" & x).Formula = "='C:My Documents[Employees.xls]Sheet1'!B" & x & "&"" ""&'C:My Documents[Employees.xls]Sheet1'!C" & x & ""
Next x

End Sub

Is this what you had in mind??

Rgds
AJ

N.B. Where ever you see two backslashes above, only use one in your formulae/code (it's just the way this website works!)

[ This Message was edited by: AJ on 2002-04-10 13:06 ]

Al Chara · Apr 10th, 2002, 02:09 PM

Try the following:

ActiveCell.Value = "= 'C:My Documents" & "[employee.xls]Sheet1'!" & Cells(1, 1).Address

(Remember this board adds an extra slash mark)

Wingz · Apr 10th, 2002, 02:10 PM

AJ - Thanks for your response. The reason it has to be in a macro is because every week, the end users need to update their timesheets with the employee list. If someone has been deleted, they should bot appear in the next week. So the macro gets run 4 times in 4 weeks, pasting the records from the employee file, into the active workbook. I will try the code and hope it works. Thanks again.

Apr 10th, 2002, 02:09 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Try the following: ActiveCell.Value = "= 'C:My Documents" & "[employee.xls]Sheet1'!" & Cells(1, 1).Address (Remember this board adds an extra slash mark) __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 01:42 PM	#1
Wingz New Member Join Date: Mar 2002 Posts: 33	Can someone PLEASE tell me how to make reference to a cell in a closed workbook WITHOUT using the R1C1 reference format. I need to use Cells(x,y)so I can loop. It may just be a syntax thing but here is what I want: activecell.value = Workbooks("C:My Documentsemployee.xls).Worksheets(1).cell(x,y).value ----> Or something to this effect. I know the syntax is off but if ANYONE can help, I would greatly appreciate it.

Apr 10th, 2002, 02:05 PM	#2
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	Ok, here goes. I've had a look at this and your earlier post and I'm not sure why this has to be in a macro but I'm sure you have your reasons. First things first, the full formula that needs to go in cell A1 of the active workbook is: ='C:My Documents[Employees.xls]Sheet1'!$B1&" "&'C:My Documents[Employees.xls]Sheet1'!$C1 As there is no dollar($) in front of the row reference, you could just copy and paste this formula down. If you wanted a macro to go through and fill each in for 75 rows then how about: Sub test () For x = 1 To 75 Range("A" & x).Formula = "='C:My Documents[Employees.xls]Sheet1'!B" & x & "&"" ""&'C:My Documents[Employees.xls]Sheet1'!C" & x & "" Next x End Sub Is this what you had in mind?? Rgds AJ N.B. Where ever you see two backslashes above, only use one in your formulae/code (it's just the way this website works!) [ This Message was edited by: AJ on 2002-04-10 13:06 ]

Apr 10th, 2002, 02:10 PM	#4
Wingz New Member Join Date: Mar 2002 Posts: 33	AJ - Thanks for your response. The reason it has to be in a macro is because every week, the end users need to update their timesheets with the employee list. If someone has been deleted, they should bot appear in the next week. So the macro gets run 4 times in 4 weeks, pasting the records from the employee file, into the active workbook. I will try the code and hope it works. Thanks again.