String problem

[LEXCERM]

Hi All,

I have the following piece of code which assigns a file name:-

Dim new_file As String
Dim direc As String
direc = "H:\"
new_file = "Report_(" & Format(Now(), "yyyymmdd-hhmmss") & ").xlsx"

I currently have it stored behind a button on a form (part of a procedure).

There are many different modules, but whenever a different module executes “new_file” it returns a blank string.

Where is the best place to store the code so that all modules recognises the string “new_file”.

Thanks in advance,
Paul.

 

[Lewiy]

Personally I would store the string in a cell in a worksheet somewhere and refer to that cell when you want it back. However, the more “proper” way to do this would be to declare the variable “new_file” as a public variable. To do this, simply declare the variable as normal but in one of the standard modules and before any sub routines, i.e:

Dim new_file as String

Sub MySub()
     Bits of code
End Sub

Sub MySecondSub()
     Bits of code
End Sub

Hope that helps!

 

[LEXCERM]

Hi Lewiy and thanks very much for replying.

Unfortunately, I still can't get it to work. Basically I have the following projects:-

Userform1
Module1
Module2
Module3
Module4
Module5

My problematic code is behind Userform1. I am trying to call "new_file" from each of the other 5 modules. As suggested, I have placed the Dim statements at the beginning of Module1 but it still will not work. I have also tried putting it at the beinninng of each module, but no luck.

Thanks again.

Paul.

 

[Lewiy]

Perhaps try putting the code which creates the new file name within its own sub in, let’s say Module1 (note: still putting the variable declaration at the very top of the Module):

Dim new_file As String

Sub NewFileName()
Dim direc As String 
direc = "H:\" 
new_file = "Report_(" & Format(Now(), "yyyymmdd-hhmmss") & ").xlsx" 
End Sub

Then in the place in the UserForm1 code where you have this at the moment, just call this Sub Routine with the line:

NewFileName

See if that helps.

 

[LEXCERM]

Hi,

Funnily enough, I have just tried exactly what you have said, but no luck. I created a separate module for the code (with the Dim statement at the top).

It runs as follows:
- procedure starts in Userform1
- halfway through it calls the sub routine in the module I have just created (the Dim has a value as expected)
- the procedure returns to Userform1
- a few lines down it calls another module
- the Dims and "new_file" are empty

It doesn't seem to store it. I must be missing something really obvious here.

Cheers,
Paul.

 

[Lewiy]

Are you able to post the UserForm1 code and a couple of the Module codes so I can see what you have?

 

[LEXCERM]

Okay, I have created this simple routine which creates the issue I an having.

Userform code:-

Private Sub CommandButton1_Click()
Run "CREATE_NEW_FILE"
MsgBox new_file
End Sub

Module1 contains the following code:

    Dim new_file As String
    Dim direc As String
Private Sub create_new_file()
    direc = "H:\"
    new_file = "Reports_(" & Format(Now(), "yyyymmdd-hhmmss") & ").xlsx"
End Sub

When the code then returns to the Userform, the msgbox which is activated is empty, thus not retaining the "new_file" details.

Rgds,
Paul.

 

[Lewiy]

Hmmm, I see what you mean, I’m having problems with this too. Umm, personally I’d go back to my original method and store the string in a cell in one of the worksheets and refer back to it.

 

[Norie]

Paul

What exactly is new_file meant to be/do?

Where is the code and userform located?

 

[LEXCERM]

Lewiy: Thanks for all your help. I took your advice and slightly adapted it. I stored the string in a field on the form itself and it works fine. Can't see why it wouldn't pick it up before. Maybe you can't cross-reference the Dim statement bewteen modules (?) Cheers.

Hi Norie,
"New_file" is the name of a file I am creating/saving. I then drag into it many text files from different sources. I created separate modules for each different text import.

Rgds,
Paul.