How do I pass the filename selected into another module?

[Mr Denove]

Morning all,

Im still learning my way through VBA and hoping for some help and guidance on this problem please.

I have a module (see blow) which selects the file to be used. Another module is used to save another file and I need to use the date which is in the filename to name the being saved.

How do I pass the name of the file selected in the below code into the other module?

Any help is greatly appreciated. Thanks in advance.
Stuart

Sub CopySourceExtract()
Dim wb1 As Workbook, wb2 As Workbook
Dim Ret1, Ret2
Dim LastRow As Long
    
Dim Filename As Variant
    


Sheets("Extract").Cells.AutoFilter

Application.ScreenUpdating = False
     
Application.DisplayAlerts = False

Application.DisplayStatusBar = False

ActiveSheet.DisplayPageBreaks = False

Application.CutCopyMode = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False
 
Set wb1 = ActiveWorkbook
        
    ChDir "\\Code that links the folders to file etc etc\"
  
    Ret2 = Application.GetOpenFilename("Excel Files (*.csv*), *.csv*", _
    , "Please select file")
If Ret2 = False Then End
    
Dim strPath As String
strPath = Ret2

Dim strFile As String
strFile = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
Filename = CreateObject("Scripting.FileSystemObject").GetBaseName(strFile)
    
Set wb2 = Workbooks.Open(Ret2, Local:=True, ReadOnly:=True)
ThisWorkbook.Activate

More code......

 

[RoryA]

I would suggest you change the code to select the file into a function, and call that from the other routine that needs the file name.

Also, don't use End on its own unless you have a very good reason. And the majority of the code you posted seems completely unnecessary just to get a file name.

 

[Mr Denove]

Hi Rory,

Thanks for the quick reply.

When you say select the file into a function can you explain / show what you mean please? Still trying to get my head round the whole passing variables etc.

Just to say its a small extract from the code I am using the, rest of the code is filtering and removing set data which is why the code on show seems a bit extravagant.

Stuart

 

[RoryA]

Does the code above call the second bit of code, or is that run completely separately?

 

[Mr Denove]

The code is in 3 seperate steps.
Open
Import
Save

I need the file in Open to be used as part of the name ie the date in the Save module

 

[RoryA]

In that case, you need to store that value somewhere. You could use a public variable as long as you remove any End statements as they will reset all variables, or you could put the value into a cell or a defined name, so that the later routine can read it.

 

[Mr Denove]

Hi,
Yes Im going down the route of using a cell value as I havent got my head round the passing variable etc whether PUblic or in a Function as you mentioned.

I really need to learn how to use them.

 

[RoryA]

If the three routines are completely separate, a function won't be much specific help (though it would make the code more reusable) since you'd have to call it again each time, which would be annoying. A Public or module-level (if all the routines are in the same module) variable would be the simplest option - you just declare the variable at the top of the module and not inside any of your routines, so that they all use the same one.

 

[Mr Denove]

ok, so I am thinking

Public Function ()
Declare the type of variable

Module 1
Module 2
Module 3

 

[RoryA]

I have no idea what that is supposed to mean, I'm afraid.