Setting a public variable as a constant

[OlorinFiresky]

Hi all,

I've got some code that calls a public variable. If I run the code a second or subsequent times it fails

I have the public variable set

Public FileName As String

in module6

The information set in the variable is gained from this bit of code

Sub ImportTextFile()
    Sheets("Raw File").Select
     
    ActiveSheet.Unprotect
    
     'Variable to capture full path of selected text file
'    Dim strFullPath As String
     
    Module6.FileName = Application.GetOpenFilename("PRN Files (*.prn),*.prn", , "Please select a prn file to import...")
    If Module6.FileName = "False" Then Exit Sub
     
       Workbooks.OpenText FileName:=Module6.FileName, Origin _
        :=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(6, 1), Array(13, 1), Array(22, 1), Array(31, 1), Array(40, 1), _
        Array(48, 1), Array(57, 1), Array(65, 1), Array(73, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Selection.Copy
    ActiveWindow.ActivateNext
    ActiveSheet.Paste
    Range("A1").Select
    
    Call Delete10Rows
End Sub

It is then called in this bit of code:

Sub SaveWorkbookAsNewFile()
    Dim ActSheet As Worksheet
    Dim fldr As FileDialog
    Dim NewFileName As String
    Dim p As Integer
  
    p = InStrRev(Module6.FileName, "\")
        If p > 0 Then
            fileNameOnly = Mid(Module6.FileName, p + 1)
        End If

Application.ScreenUpdating = False    ' Prevents screen refreshing.
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fldr
        .Title = "Select a folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then
        MsgBox "You must select a folder"
        Exit Sub
        End If
        selectedfolder = .SelectedItems(1) & "\"
    End With
    
ActiveWorkbook.SaveAs FileName:=selectedfolder & Replace(LCase(fileNameOnly), ".prn", ".txt"), _
            FileFormat:=xlText, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False
    For Each wb In Application.Workbooks
    If Not wb.name = "New New TOPAS to PLS-CADD Converter.xlsm" Then
            wb.Close SaveChanges:=False
    End If
        Next
    Sheets("Instructions").Select
    Application.ScreenUpdating = True
End Sub

If this second lot of code needs to be run multiple times it fails as the Module6.FileName variable seems to empty after it is used the first time. I need to find a way for that variable to retain the information until it is overwritten by the first lot of code being run again, so that if I need to run the second lot of code prior to the first lot of code being run again, it will work without issue.

I hope that isn't too confusing a statement.

Any help that can be offered would be greatly appreciated!

 

[tweedle]

Welcome!

I don't see any issue in your code - rather well written actually
nor can replicate the behavior you describe.

 

[OlorinFiresky]

Hi tweedle,

Perhaps the difference is in the fact that my Excel 2007 is a published app through Citrix XenApp rather than a physical install?

 

[tweedle]

I wouldn't suspect the install-platform in a case as this.

I would put a watch on Module6.FileName , set to Break when it changes.
That should give you a sharpened perspective on where the variable gets cleared.

 

[pcc]

I would avoid the use of FileName as a variable as it is a 'key' word in VB. Try changing it to myFileName..

 

[OlorinFiresky]

Hi pcc,

Many thanks for your kind words. Changing the variable to myFileName seems to have resolved the issue!