VBA Text Output but user can select location

[David20]

I'm using VBA to take the contents of a certain range an output it as a text file. The file is actually XML data for upload to another system, so the text file has to have a specific extension, "filename.nrc".

I originally designed it to always save to the Documents folders in the users local profile. Everything works fine on my computer, but I had somebody else test it and we couldn't find the file after it saved. Most users will be logging into a virtual desktop (remote) system, which I think is part of my problem.

Is it possible to modify my existing code to allow the user to select that save location but still output as a *.nrc file? (This is only the "OutPut" portion of my code, not the whole sub.)

FF = FreeFile
  Dim FileName As String
    FileName = InputBox(Prompt:="Please enter the file name.", Title:="Save file as", Default:="XML_Data_Import_" & Format(Date, "yyyy-mm-dd"))
MsgBox "Your Data Import file will be saved to your Documents folder."
  Open Environ("UserProfile") & "\Documents\" & FileName & ".nrc" For Output As #FF
  Print #FF, TextOut
  Close #FF

As always, any help is greatly appreciated!

 

[Skyybot]

You can have a user choose the destination folder by using Application.GetSaveFilename in your code.

 

[Logit]

Admittedly I could be in error, but after researching the internet this is what I've learned ...
.... Your ".nrc" file is just a TEXT FILE with the extension of .nrc .
... When using FREE FILE it appears the location where the file is opened is the same location where the file will be saved because you are simply CLOSING the file.
... I wasn't able to locate any website that spoke to selecting another SAVE location.

I'll monitor this thread to see if my findings were incomplete and what new stuff I might learn.

 

[David20]

Logit, you're correct. Wherever it's opened from is where it will be closed to (saved).

Skyybot, you're recommendation led me down a Google path that ended up with a solution, but it's admittedly a bit clunky. I ended up using a Function to pick a folder and then called the results back during the Open/Close code.

  Dim FileName As String
    FileName = InputBox(Prompt:="Please enter the file name.", Title:="Save file as", Default:="Data_Data_Import_" & Format(Date, "yyyy-mm-dd"))
MsgBox "The next window is to select the folder to save your Data Import file." & vbCrLf & "You're not re-naming your file, just selecting where to save it." _
& vbCrLf & "You named your file in the previous step."
  Open GetFolder() & "\" & FileName & ".nrc" For Output As #FF
  Print #FF, TextOut
  Close #FF



Function GetFolder() As String

Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    '.InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
  
End Function

Thanks for your help.

 

[Logit]

David20:

Using your FUNCTION, I keep receiving a "Bad File Name" error. Are you seeing the same there ?

 

[David20]

David20:

Using your FUNCTION, I keep receiving a "Bad File Name" error. Are you seeing the same there ?

There's more code than what I posted here, I'd guess that's what's happening for you. I'm happy to post it all, if you're interested but I've already left work for the day so I can't until Monday. For what it's worth, it works for me and a coworker.

 

[Logit]

I would be interested in seeing the remainder of the code. Interesting concept.

 

[David20]

@Logit, here is all of the code. I don't think it matters, but I have them both in the same module.

My workbook has two worksheets, "Data Conversion" and "nrc text". There are only three basic steps:
1. The user inputs data into "Data Conversion".
2. Formulas on "nrc text" format the data with the needed XML tags, basically ="XML Tag" & 'Data Conversion'!$A$1 & "/XML Tag>".
3. User clicks a button and the VBA code saves the text from "nrc text" as a text document with the "*.nrc" extension so it can be uploaded into another system.

As I said, it's not an elegant solution, but it works. I'm still a VBA beginner so I'd be curious to know if you see any ways to improve it. (I shamelessly plagiarize code!)

Sub TestCode()
'ScreenUpdate: Off
    Application.ScreenUpdating = False
    On Error Resume Next
Sheets("nrc text").Visible = True
Sheets("nrc text").Activate
Dim R As Long, C As Long
Dim FF As Long
Dim TextLine As String, TextOut As String
Dim Data As Variant
Data = Range("'nrc text'!$A$1:$A$286")
    For R = 1 To 286
TextLine = ""
    For C = 1 To 1
TextLine = TextLine & vbTab & Cells(R, C)
    Next
        TextOut = TextOut & vbCrLf & Mid(TextLine, 2)
    Next
        TextOut = Mid(TextOut, 3)
FF = FreeFile

'File Name
  Dim FileName As String
    FileName = InputBox(Prompt:="Please enter the file name.", Title:="Save file as", Default:="Data_Import_" & Format(Date, "yyyy-mm-dd"))
MsgBox "The next window is to select the folder to save your Data Import file." & vbCrLf & "You're not re-naming your file, just selecting where to save it." _
& vbCrLf & "You named your file in the previous step."
  Open GetFolder() & "\" & FileName & ".nrc" For Output As #FF
  Print #FF, TextOut
  Close #FF
Sheets("nrc text").Visible = False
Sheets("Data Conversion").Activate
'ScreenUpdate: On
    Application.ScreenUpdating = True
End Sub



Function GetFolder() As String

Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    '.InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
   
End Function