OLE Object Set Variable Source Doc

[GemaDilemma]

I have a problem that has stumped me for the last day.

I have a GetUserName Api which extracts the name of the current user, it works perfectly.

I also have an unbound OLE Object that is being controlled on form load by this.


With Me.OLE1
.Class = "Excel.Sheet"
.OLETypeAllowed = acOLELinked
.SourceDoc = "C:\Users\" & GetUserName & "\Desktop\Database\Excel Files\Bulk Updating.xlsm"
.SourceItem = "R1C1:R5C5"
.Action = acOLECreateLink
.SizeMode = acOLESizeClip
End With

When I run the code I get Runtime Error 2101 on Line 4.

When I set Line 4 to Jblogs instead of my get user function it works fine.

Any Help?

Gema

 

[DavidAtWork]

have you tried debugging it to see if there is a value returned by GetUserName

David

 

[DrallocD]

Is it related to this thread?
http://www.access-programmers.co.uk/forums/showthread.php?t=252603

 

[DavidAtWork]

try this to see if your GetUserName is returning a value

Dim userName as String
 
userName = GetUserName
 
With Me.OLE1 
.Class = "Excel.Sheet"
.OLETypeAllowed = acOLELinked 
.SourceDoc = "C:\Users\" & userName & "\Desktop\Database\Excel Files\Bulk Updating.xlsm"
.SourceItem = "R1C1:R5C5"
.Action = acOLECreateLink 
.SizeMode = acOLESizeClip 
End With

This will allow you to put a break in the userName = GetUserName line to see if GetUserName is working

David