unable to load & save a workbook from a VBA function

Here's what I have so far

Option Explicit

Function LRS(ByVal instring As String)

 ' where instring is the name of an xlsx workbook
 
 Dim result As Integer
  
 Dim rootPath As String
    rootPath = ThisWorkbook.Path
    rootPath = rootPath & "\"
    'MsgBox "root path " & rootPath

  
 Dim Filename As Variant
 Filename = rootPath & "\" & instring & ".xlsx"
 If instring <> "" Then
    result = LRS_do_one(Filename)
 End If
  
 End Function

Function LRS_do_one(Filename)
  
 MsgBox "Doing " & Filename     ' gives popup w expected results
 
 Workbooks(Filename).Open       ' doesn't to anything
 Workbooks(Filename).Activate   ' doesn't to anything
 Workbooks(Filename).RefreshAll ' doesn't to anything
 Workbooks(Filename).Save       ' doesn't to anything
 Workbooks(Filename).Close      ' doesn't to anything
  
End Function

Function LRS(ByVal instring As String)
 ' where instring is the name of an xlsx workbook
  Dim result As Integer, rootPath As String
  rootPath = ThisWorkbook.Path 
  Dim Filename As String
 Fname = rootPath & "\" & instring & ".xlsx"
 If instring <> "" Then
    Workbooks.Open Filename:=Fname
 end if
End Function

please check commands sintax on Help, don't create personal commands

still doesn't work .... here's what I have now

Option Explicit

Function LRS(ByVal instring As String)

 ' where instring is the name of an xlsx workbook
 
 Dim result As Integer, Fname As String, Filename As String
  
 Dim rootPath As String
    rootPath = ThisWorkbook.Path
    rootPath = rootPath & "\"
    'MsgBox "root path " & rootPath
  
 Fname = rootPath & "\" & instring & ".xlsx"
 If instring <> "" Then
    MsgBox "Doing " & Fname
    Workbooks.Open Filename:=Fname
    'Workbooks.Activate Filename:=Fname
    'Workbooks.RefreshAll Filename:=Fname
    'Workbooks.Save Filename:=Fname
    Workbooks(Filename).Close
     
 End If
  
 End Function

thanks

Where are you trying to use the function?

If it's on a worksheet then it's not going to work.

Can you just clarify exactly where you are trying to use the function?

Are you trying to use it in a cell?

why do you use a function and not a sub ? try this

sub LRS()
Dim result As Integer, Fname As String, Fame As String, instring as string
Dim rootPath As String
rootPath = ThisWorkbook.Path
instring = "yourfilename" ' <<<<<<<<<< change it
Fname = rootPath & "\" & instring & ".xlsx"
If instring <> "" Then
MsgBox "Doing " & Fname
Workbooks.Open Filename:=Fname
'Workbooks.Activate Filename:=Fname
'Workbooks.RefreshAll Filename:=Fname
'Workbooks.Save Filename:=Fname
'Workbooks(Filename).Close

End sub

no joy ... here's what I have

Option Explicit

Sub LRS(ByVal instring As String)
Dim result As Integer, Fname As String, Fame As String
Dim rootPath As String
rootPath = ThisWorkbook.Path
'instring = "Funds.Fidelity"  ' <<<<<<<<<< change it
Fname = rootPath & "\" & instring & ".xlsx"
If instring <> "" Then
MsgBox "Doing " & Fname                     ' looks ok
Workbooks.Open Filename:=Fname              ' doesn't do anything
'Workbooks.Activate Filename:=Fname
'Workbooks.RefreshAll Filename:=Fname
'Workbooks.Save Filename:=Fname
'Workbooks(Filename).Close
End If
End Sub

Paul

Are you going to reveal where you are trying to use this and how it isn't working?

If you are trying to use it in cell on a worksheet it just isn't going to happen.

PS Please use code tags when posting code, kind of a rule.

You can't use a user defined function on a worksheet to do what you want.

Yes, but not with a UDF.

you can use a sub, as I showed you

Paul,

Welcome to the forum.

I have added code tags to all your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

ok ... now using a button to invoke LRS_List() ... making progress, but still doesn't work
automatic calculations a load time never complete .... see line by line comments

Option Explicit
Sub LRS_List()
 
Dim rootPath As String
rootPath = ThisWorkbook.Path
'MsgBox "Doing " & rootPath

Dim Fname As String
Dim file As String
file = "Funds.Fidelity"
Fname = rootPath & "\" & file & ".xlsx"
LRS (Fname)

End Sub
Sub LRS(Fname)
Dim res As Integer
MsgBox "Doing " & Fname             ' looks ok !
Workbooks.Open Filename:=Fname      ' loads it OK !
'Application.CalculateFullRebuild   ' no effect
DoEvents                             ' automatic calculations never complete  
ActiveWorkbook.RefreshAll           ' WEB queries, seems to execute, status bar
DoEvents
 'ActiveWorkbook.Save                'Saves it (commented out for debugging)
ActiveWindow.Close                  'Closes it, prompts to save
End Sub