Open, Save and Save As (contents of a text file in Userform)

[inddon]

Hello There,

I have a requirement to Open (display contents), Save and Save As (save the contents to a file), text files to be working in a Userform.

I have attached a sample workbook with the Userform and explanation for your reference.

Could you please advice, how this can be developed in VBA?


















Many thanks and look forward to hearing from you.

Regards,
Don

 

[Marc L]

Hi,​

​

as per your explanation obviously replace the TextBoxt D by a Label - read only - then attach your corrected workbook …​

​

As Excel is far not a text editor so why using Excel instead of any text editor ?‼​

​

 

[inddon]

Thank you Marc for your reply.

I have changed the steps accordingly and also ammended the Save As step to overwrite file if exists. Also attached the excel file. Hope my requirements are clear




In this requirement I am displaying the SQL query of a file which I later use it to execute it against the database. The purpose is the User can open the file make modification to the SQL query and execute it and later save the changes to a file.

Look forward to hearing from you

Regards,
Don

 

[Marc L]

As D is still a TextBox instead of a Label … ​

​

And why not using directly a text editor rather than reinventing the wheel under Excel with the risk of a square one ?‼​

​

 

[inddon]

As D is still a TextBox instead of a Label … ​

​

And why not using directly a text editor rather than reinventing the wheel under Excel with the risk of a square one ?‼​

​

Hi Marc,

Do you mean to use a control text editor instead of a text box? If that is the case then I did not know if such a control exists and how to add it.
I have replaced the TextBox D with a Label.

Attached the new version of the sample workbook.

Regards,
Don

 

[Marc L]

No, why not a text editor - Notepad, WordPad, Notepad++, Word, … - rather than a poor UserForm under Excel ?!​

​

 

[inddon]

Hi Marc,

The excel application is build to connect to a database to fetch data, that is based on static sql queries (quite a few) stored within the excel application. This has increased the size of the file and is difficult to maintain it as well. The plan is to make it more flexible and move these static queries and store them as individual text files.

The purpose of this userform is to facilitate the user to execute these individual sql queries:
1. select the sql queries from these files (display/read on the screen), so they know which sql they are running
2. modify (as needed),
3. save it in the file or in a different file (save as)
4. execute it from the userform

Hope this helps to understand the requirement.

Regards,
Don

 

[Marc L]

According to your last attachment nothing matches your added point #4 !​

If you do not already have the code for this point #4 so coding previous points is useless …​

​

As under Excel it can be a mess - for coder like for users - if you already have the point #4 code​

attach at least a real text file in order some helper may have an idea of where to go rather than a blind way …​

​

 

[inddon]

Hi Marc,

The description from point 1 to 4 was the explanation of the process as to the why question, from you.
Point 4 is not included in the workbook, this will be worked out by the database people.

For me the requiirement is only from 1 to 3 to be working in the VBA userform (as per post 3 explanation).

Regards,
Don

 

[Marc L]

You must first disactivate each 'Save' button with its Enable property to False​

and set the Multiline property to True of the text file content TextBox …​

​

For the Open button you can start with GetOpenFilename method to choose the file text​

then reach it with Open statement and directly allocate the TextBox at once using Input function​

combined with LOF function then use Close statement.​

​

 

[inddon]

Thank you Marc for the tips.

What I am looking for is the actual VBA code of how this can be implemented. I would appreacite your help towards it.

Thanks again and look forward to hearing from you.

Regards,
Don

 

[Marc L]

It was an answer as an advice, in fact what you asked for in your initial post :​

Could you please advice, how this can be developed in VBA?

​

Anyway I send it to a kid as an exercice as any beginner can achieved this,​

I will see when I will be back to my emails computer and check his work …​

Maybe other helpers here will give it a try, even beginners.​

​

 

[inddon]

Hi Marc,

My understanding of VBA is very limited. The reason I have joined the group is to learn from people who are experts. To study how their code is written and progress further and also have respect for each other.

For your reference: yesterday, managed to put this together from various sources. Thank you for your VBA tips too. For the other option it still needs to be done. Any help from anywhere is much appreciated.

Sub GetFilePathBasic()
  Dim xfilePath, xfileName As String
  Dim xtextData As String, xFileNumber As Integer
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "SQL script files"
    .Filters.Add "SQL Files", "*.sql", 1
    .AllowMultiSelect = False
      
    'File picker dialog box
    If .Show <> 0 Then
      xfilePath = .SelectedItems(1)      'Get the complete file path.
      xfileName = Dir(.SelectedItems(1)) 'Get the file name.
      FrmSQL.TextBoxSQLxfileName.Value = xfilePath
      FrmSQL.TextBoxSQLxfileName.ControlTipText = xfilePath
    
      xFileNumber = FreeFile
 
      Open xfilePath For Input As xFileNumber
      xtextData = Input$(LOF(xFileNumber), xFileNumber)
      Close xFileNumber

      FrmSQL.TextBoxSQLScript.Value = xtextData
      FrmSQL.TextBoxSQLScript.SetFocus
    End If
  End With
End Sub

Thanks again and look forward to hearing from you.

Regards,
Don

 

[Marc L]

According to your posts #5 & 9 attachments and after the necessary corrections of the UserForm which must be already done​

for your reference this is the kid VBA beginner starter to paste to the UserForm code module :​

Const T = "Text Files, *.txt"

Private Sub ButtonOpen_Click()
    V = Application.GetOpenFilename(T):  If V = False Then Exit Sub Else LabelFilePath = V
    F% = FreeFile:  Open V For Input As #F:  TextBoxFileContents = Input(LOF(F), #F):  Close #F
    ButtonSave.Enabled = False:  ButtonSaveAs.Enabled = False
End Sub

Private Sub ButtonSave_Click()
    F% = FreeFile:  Open LabelFilePath For Output As #F:  Print #F, TextBoxFileContents;:  Close #F
End Sub

Private Sub ButtonSaveAs_Click()
    V = Application.GetSaveAsFilename(LabelFilePath, T):  If V <> False Then LabelFilePath = V: ButtonSave_Click
End Sub

Private Sub TextBoxFileContents_Change()
    ButtonSave.Enabled = True:  ButtonSaveAs.Enabled = True
End Sub

Private Sub UserForm_Initialize()
    ButtonOpen_Click
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[inddon]

According to your posts #5 & 9 attachments and after the necessary corrections of the UserForm which must be already done​

for your reference this is the kid VBA beginner starter to paste to the UserForm code module :​

Const T = "Text Files, *.txt"

Private Sub ButtonOpen_Click()
    V = Application.GetOpenFilename(T):  If V = False Then Exit Sub Else LabelFilePath = V
    F% = FreeFile:  Open V For Input As #F:  TextBoxFileContents = Input(LOF(F), #F):  Close #F
    ButtonSave.Enabled = False:  ButtonSaveAs.Enabled = False
End Sub

Private Sub ButtonSave_Click()
    F% = FreeFile:  Open LabelFilePath For Output As #F:  Print #F, TextBoxFileContents;:  Close #F
End Sub

Private Sub ButtonSaveAs_Click()
    V = Application.GetSaveAsFilename(LabelFilePath, T):  If V <> False Then LabelFilePath = V: ButtonSave_Click
End Sub

Private Sub TextBoxFileContents_Change()
    ButtonSave.Enabled = True:  ButtonSaveAs.Enabled = True
End Sub

Private Sub UserForm_Initialize()
    ButtonOpen_Click
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Thank you Marc for the kid nutritional beginner starter code . This helps me in growing in VBA.

Regards,
Don

 

[Marc L]

Yes his logic is perfect !​

​

I just added the constant, the variable type declaration character and like explained in post #10​

I directly allocated the UserForm elements rather than using useless variables …​

​

 

[inddon]

The other nice thing is I see in your code, how you can write conditions in one line (eg. the If else condition in one line)

Regards,
Don