Loop through Excel references and replace with form Control

[rudeboymcc]

Hi. Ive got a template in Excel which has references in it that have the same name as the controls on the form that i want in the template.

so far I have:

Dim objExcel As Excel.Application
Dim oReference

Set objExcel = New Excel.Application
 Set oWB = objExcel.Workbooks.Open("Y:\RDN.xls")
        objExcel.Visible = True
        oWB.Sheets(1).Range("PeriodFrom").Value = Me.PeriodFrom
       
   
        For Each oReference In Application.References
            oWB.Sheets(1).Range(oReference).Value = 
                     frmCurrentForm.Controls(oBookmark)

        Next

THe For loop works and loops through all the references (I used a msgbox). THe first test line:

 oWB.Sheets(1).Range("PeriodFrom").Value = Me.PeriodFrom

Works fine.

so the line that isn't working is the one inside the for loop.

Basically how do I select a range with name oReference?

 

[DJkarl]

Dim objExcel As Excel.Application
Dim oReference
 
Set objExcel = New Excel.Application
 Set oWB = objExcel.Workbooks.Open("Y:\RDN.xls")
        objExcel.Visible = True
        oWB.Sheets(1).Range("PeriodFrom").Value = Me.PeriodFrom
 
 
        For Each oReference In Application.References
[COLOR=red]            oWB.Sheets(1).Range(oReference).Value = [/COLOR]
[COLOR=red]                     frmCurrentForm.Controls(oBookmark)[/COLOR]
 
        Next

so the line that isn't working is the one inside the for loop.

Basically how do I select a range with name oReference?

So basically you are trying to reference a Range by using the value inside it, you can't do that. Excel needs a unique way to reference a Range, because every cell on a sheet could have the same value you need to pass it an Address that cannot change.

What you could do in this case is in addition to looping through the Application.References you would need another loop that would go through the possible Ranges in excel checking to see if the current value = your reference name then do the update and move to the next reference.

 

[rudeboymcc]

So basically you are trying to reference a Range by using the value inside it, you can't do that.

No Not quite. I'm trying to reference a range using the list of Names in the excel sheet.

Slight improvement today:

       For Each oReference In ActiveWorkbook.Names
            MsgBox "Found " & oReference.Name
            oWB.Application.Goto Reference:=oReference.Name
            ActiveCell.Value =  "Test"
        Next

This goes through the excel sheet, and for every Reference it msgbxs:
"Found Address" (Address is the first Named Range)

THe next two lines activate the cell, and then change that cell's value to "Test". This works fine.

Now I want to change

ActiveCell.Value =  "Test"

To

ActiveCell.Value =  Me.Controls(oReference.Name)

so it looks to my form with a control with name oReference.Name for the value.

How do i code this?

 

[rudeboymcc]

:-(

Made a type in my excel names and the control on the form with that name didn't exist. how stupid do i feel?