referencing cell address

[cereldine]

Could somene point me in the right direction with the below code, this
particular code is going to be part of a larger procedure but i would
like to understand this first.

The problem i'm having is i don't know syntax to reference a cell
address,
meaning that the range variable gets set to the text value of A1
instead of A1 itself, this messes up the rest of the statement. The bit
i would like some help on is in green
thanks

Public Sub export()
Dim rng As Range
Dim temprng As Range
Dim rngName As String

Range("A1").Select

Set rng = ActiveCell
do
rngName = rng.Text


MsgBox "Filepath " & rngText

temprng = Cells(rng).Offset(1, 0).Row
rng = temprng
Loop Until rngName = ""

End Sub

 

[Zack Barresse]

Hi there,

Change this ...

Cells(rng).

... to this ..

rng.

As you set 'rng' as a range object, you use it as such. The way you have it
you're trying to pass 'rng' as an argument property instead of an object.

HTH

 

[Guest]

yes zack is correct but you also need to put in a SEt and remove Row from the
end of that line of code. Also a minor mistake in your msgbox, rngText
should be rng.Text


this is how it should look

Range("A1").Select

Set rng = ActiveCell
Do
rngName = rng.Text


MsgBox "Filepath " & rng.Text

Set temprng = rng.Offset(1, 0)
rng = temprng
Loop Until rngName = ""

 

[cereldine]

Thanks, this set me on right track although i had to change last part to
this to avoid endless loop

Set rng = rng.Offset(1, 0)

Loop Until rng = ""

Another question im using this to now open another workbook,

If MsgBox("Use This Filepath " & rngName & " ?", vbYesNo) = vbYes Then
Workbooks.Open (rngName)

How can i ammend this to point at a specific workSHEET in question?

Woulkd something like Workbooks.Open (rngName)&"Sheet2" work?

Also finally does the docmd functionality work in excel? This is
something ive used alot in Access vba

 

[paul.robinson]

Hi
Workbooks.open(rngName) 'now active
ActiveWorkbook.Worksheets.Sheets2.Activate
or
ActiveWorkbook.Worksheets("Sheet2").Activate
or
ActiveWorkbook.Worksheets(2).Activate

don't know about docmd sorry

regards
Paul