• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

To create a macro to name the open unsaved .txt files into a designated directory

What: alot of .txt files that are unsaved at the taskbar

Macro to do: To save each .txt files in the sequence that i have opened and placed text in and name them as follows:

yyyy.mm-1.txt,
yyyy.mm-2.txt
.
.
etc

but the catch is how does the macro know what month or year to use?

is it possible to have a pop up dialog for me to key in the yyyy-mm so that the rest of the files can follow?

and then also a dialog for me to type out where to place the txt files.
0
ceneiqe
Asked:
ceneiqe
  • 28
  • 8
  • 7
  • +5
2 Solutions
 
FaustulusCommented:
I have little doubt but that it is possible to do what you want. Unfortunately, I am even more sure that I wouldn't even try to do it. You see, NotePad isn't an MS Office application, it is a text editor embedded in Windows. Now, you are asking an Office Application (for example, Excel, but it doesn't matter which) to ask Windows for access to the list of files that NotePad currently has open. VBA's access to Windows is very limited and poorly developed.
I would suggest that you change your workflow. If, for example, VBA created the TXT file it could be saved by any name you like. You could open the files created by VBA in NotePad after they are saved because they are regular TXT files. As an alternative you could save them manually by any name you like and later run a program that renames them. For example, you could save them as 1.txt, 2.txt, 3.txt etc. and the renaming program would later add the date.
The date could be determined the way you suggest if it isn't easier to take year and month from the current or yesterday's date. I suppose, even the creating date of the TXT file could be made available - once the file was created, meaning saved.
0
 
Helen FeddemaCommented:
I agree about the difficulty of dealing with open Notepad files.  For the yyyy.mm string, if it is not to be derived from the current date, you could pop up an InputBox:

Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
Dim strResult As String

strTitle = 
strPrompt = 
strDefault = 
strResult = InputBox(prompt:=strPrompt, _
   title:=strTitle, _
   default:=strDefault)

If strResult = "" Then
   'User canceled
   GoTo ErrorHandlerExit
End If

Open in new window

For the save folder, use the Office FileDialog object (modify the code below as needed):
Private Sub cmdDocsPath_Click()
'Created by Helen Feddema 28-Nov-2005
'Modified by Helen Feddema 29-Sep-2008

On Error GoTo ErrorHandler
   
   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   Set txt = Me![txtDocsPath]
   
   With fd
      .Title = "Browse for folder where __________"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      '.InitialFileName = strPath
      If .Show = -1 Then
         strPath = CStr(fd.SelectedItems.Item(1))
         txt.Value = strPath
         strPropertyName = "DocumentsPath"
         strPropertyValue = strPath & "\"
         lngDataType = dbText
         Call SetProperty(strPropertyName, lngDataType, _
            strPropertyValue)
      Else
         Debug.Print "User pressed Cancel"
      End If
   End With

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
aikimarkCommented:
You want a program to find your open Notepad sessions and, if unsaved, save each one in the order it was opened?

I don't think constructing the name of the file will be difficult, but I anticipate the window iteration will be tricky/difficult/non-trivial.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ceneiqeAuthor Commented:
hi helen

may i know where should i store the macro ?
I don't have VB.net

i tried storing in excel module but doesn't work.
0
 
Helen FeddemaCommented:
If you mean my code, it should run from Access, Word or Excel, so long as you set the Office reference.  The code will need to be tweaked depending on how you are going to use it -- the cmdDocsPath code runs from an Access command button and places the selected folder name in a textbox, but you can modify it to a function that returns the folder path.
0
 
ceneiqeAuthor Commented:
Hi could you kindly paste the script where i can it from Excel ?

Thanks.
0
 
ceneiqeAuthor Commented:
i am putting all the macros in excel so that i can just ALT+F8  to run the scripts.
0
 
ceneiqeAuthor Commented:
If you mean my code, it should run from Access, Word or Excel, so long as you set the Office reference.

What do you mean by set office reference ?
How is it done?
0
 
LeeTutorretiredCommented:
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
0
 
ceneiqeAuthor Commented:
i believe my questions are clear enough for comments or answers.
0
 
ceneiqeAuthor Commented:
i believe my questions are clear enough for comments or answers.
0
 
Jim P.Commented:
There are any number of text editors out there, for free, that support multiple text file types from plain .txt to .vbs. to .cmd and can be scripted to do what you want internally.

Look at Notepad++, ConTEXT, or TextPad. Why reinvent the wheel?
0
 
dbbishopCommented:
Agree with jimpen, use a text editor that has that capability built in. But, if the wheel must be invented, "I don't have VB.NET is not an excuse!" Go to http://www.visualstudio.com/en-US/products/visual-studio-express-vs
0
 
ceneiqeAuthor Commented:
ok let me try out and see if it works. thks
0
 
ceneiqeAuthor Commented:
i tried notepadd ++ but there is error.

vb error
i save the 2 scripts in notepad under file name .... .vbs and then run but the above error occur.
pls advise. thks
0
 
ceneiqeAuthor Commented:
Agree with jimpen, use a text editor that has that capability built in. But, if the wheel must be invented, "I don't have VB.NET is not an excuse!" Go to http://www.visualstudio.com/en-US/products/visual-studio-express-vs 

I didn't state any excuses but i asked questions as to how to evoke the script so as to run. - if you have read my questions correctly.

I am not trained in vb script so i didn't know what kind of script command will work in what sort of environment. i am only familiar with working from script in MS excel > Microsoft Visual Basic for applications, placing them in modules and run from there.
0
 
ceneiqeAuthor Commented:
There are any number of text editors out there, for free, that support multiple text file types from plain .txt to .vbs. to .cmd and can be scripted to do what you want internally.

Look at Notepad++, ConTEXT, or TextPad. Why reinvent the wheel?

I didn't say i want to reinvent the wheel.
anyway i have downloaded Notepad++ and tried.
see my comments above
0
 
aikimarkCommented:
VBS is a different language standard than VBA.  If you are trying to run the posted VBA code in a VBScript environment, you will need to tweak the code to meet the VBS language standard.
0
 
ceneiqeAuthor Commented:
VBS is a different language standard than VBA.  If you are trying to run the posted VBA code in a VBScript environment, you will need to tweak the code to meet the VBS language standard.

I just need script that can be run in vbavba.png
0
 
aikimarkCommented:
@ceneiqe

What code was running when you received your scripting host error?
0
 
ceneiqeAuthor Commented:
1) When i run the following code,
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
Dim strResult As String

strTitle = 
strPrompt = 
strDefault = 
strResult = InputBox(prompt:=strPrompt, _
   title:=strTitle, _
   default:=strDefault)

If strResult = "" Then
   'User canceled
   GoTo ErrorHandlerExit
End If 

Open in new window


this error occur
vba error 1


2) When i run the following code,
Private Sub cmdDocsPath_Click()
'Created by Helen Feddema 28-Nov-2005
'Modified by Helen Feddema 29-Sep-2008

On Error GoTo ErrorHandler
   
   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   Set txt = Me![txtDocsPath]
   
   With fd
      .Title = "Browse for folder where __________"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      '.InitialFileName = strPath
      If .Show = -1 Then
         strPath = CStr(fd.SelectedItems.Item(1))
         txt.Value = strPath
         strPropertyName = "DocumentsPath"
         strPropertyValue = strPath & "\"
         lngDataType = dbText
         Call SetProperty(strPropertyName, lngDataType, _
            strPropertyValue)
      Else
         Debug.Print "User pressed Cancel"
      End If
   End With

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


this error occur.
vba error 2
0
 
aikimarkCommented:
1. I wouldn't classify the first one as an error. Based on the dialog box, it is the result of a help about action.

2. There IS a problem with lines 6-8.  You are supposed to type something on the right side of the equals sign.
strTitle = 
strPrompt = 
strDefault = 

Open in new window


3. It appears that you are running the second code inside a VBScript environment, based on the dialog message.
0
 
ceneiqeAuthor Commented:
2. There IS a problem with lines 6-8.  You are supposed to type something on the right side of the equals sign.

I don't know what to type.


3. It appears that you are running the second code inside a VBScript environment, based on the dialog message.

is it possible to use VBA instead of VBS ?
0
 
ceneiqeAuthor Commented:
I have increased points from 250 to 400.
0
 
aikimarkCommented:
I don't know what to type.
You should type something to the right of the equals sign.  Look at each of these three statements.  They assign a value to a variable.
0
 
ceneiqeAuthor Commented:
for example ?
what format must i adhere?
0
 
aikimarkCommented:
strTitle = "This is my title"

Open in new window

0
 
ceneiqeAuthor Commented:
and then

strPrompt = ?
strDefault = ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Just to show you can do it (and because I do not see why you should use Excel VBA for this), this code should do to ask for a folder, and use the current year and month to build the textfile names. Save it into a .ps1 file, and run it with
  PowerShell C:\ThePath\TheFile.ps1
cls
Set-StrictMode -version latest

Add-Type -Assembly Microsoft.VisualBasic
Add-Type -Assembly System.Windows.Forms

$folder = read-Host 'Please provide the target folder for unsaved Notepad files:'
$yymm = Get-Date -Format 'yyyy.MM'
$index = dir $folder\$yymm* | sort name -Descending | select -First 1 -ExpandProperty Name
if (!$index) {
  $index = 1
} else {
  $index = [int] $index.Replace('.txt','').Replace($yymm+'-','')+1
}

Get-Process 'notepad*' | 
  ? { $_.MainWindowTitle -eq 'Untitled - Notepad' } |
  sort StartTime | % {
    [Microsoft.VisualBasic.Interaction]::AppActivate($_.ID)
    sleep -m 100
    [System.Windows.Forms.SendKeys]::SendWait('^s')
    sleep -m 500
    [System.Windows.Forms.SendKeys]::SendWait("$folder\$yymm-$index.txt%s")
    sleep -m 500
    [System.Windows.Forms.SendKeys]::SendWait("%{F4}")
    $index++
  }
  

Open in new window

The timing is generous; it will last more than one second for each file to get saved, but you can change that by reducing the sleeping times (which are in milliseconds).
The code used is very similar to VBS or VBA, but PS is much easier to handle.
Since we have to use the SendKey method, which requires to have the correct window on front, you should not touch the keyboard or mouse while running the script.
0
 
aikimarkCommented:
@ceneiqe

I have shown you the format of a syntactically correct value assignment statement.  It is up to you to determine the (literal) string values.
0
 
ceneiqeAuthor Commented:
File C:\file.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help abo
ut_signing" for more details.
At line:1 char:12
+ C:\file.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException


I think i can't run in powershell as I do not have the administrator rights.
So that is why I resort to Excel Vba.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Blocking PS scripts is the default setting on Windows client OS, for security reasons. The same reason why you can't just doubleclick on a .PS1 file to execute it.

You need to do the following once on each client PC wanting to execute a (locally stored) script:
powershell -command "set-ExecutionPolicy RemoteSigned -Force"

Open in new window

That allows to run scripts, if they are on trusted locations or your local disk. (Scripts you download from Internet will need a valid certificate, so no risk.)
0
 
ceneiqeAuthor Commented:
also can't access.

Powershell access denied
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, you need to either run that in an Administrative console (elevated), so it can get applied to your computer policies,
or only change your own setting with
powershell -command "set-ExecutionPolicy RemoteSigned -Force -Scope CurrentUser"

Open in new window

0
 
ceneiqeAuthor Commented:
But now nothing happens.

Powershell Force scope- try2
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The command you show in the screenshot is neither of the commands I did ...
0
 
ceneiqeAuthor Commented:
ops sorry i think i missed out "-Scope CurrentUser" will try again..
0
 
ceneiqeAuthor Commented:
This time i can save the files, but there are 2 files can't be saved and there is an error message.

error msg after saving 4 files

picture to show files saved
0
 
ceneiqeAuthor Commented:
There were 6 files and only 4 were saved and i was prompted to save the other 2 files.
(see above 2nd image)
0
 
ceneiqeAuthor Commented:
I need to save 31 files. (assume there are 31 days in a month and 1 data file each day)
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That procedure never can be perfect, but it is the best you can get probably. Just call the script until no notepad is left. And don't touch anything while running it ;-).
0
 
ceneiqeAuthor Commented:
ok, now it seems that the script can only save 3 files and then have to call script again.
another thing is the script doesn't work on the same folder with existing notepad files. i need to use another folder.
is this correct ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I'll have to test that thesis about "3 notepad restriction" - I can't see any reason for that.
And yes, if a new file is tried to save over an existing one, you are asked for confirmation. You can either change the file name or the folder to save the file, or confirm to overwrite. But that should not happen, the script checks for the highest file number, and starts one above that, incrementing with each notepad.
0
 
ceneiqeAuthor Commented:
No, the file will try to save as the name of the directory if after 3 files.
ie. Under the "save as" field, it states "C:\user........\test5Feb14\x.txt" as the name to save.
(where x = to the sequence number of the file that it is trying to save)
0
 
ceneiqeAuthor Commented:
Have you test the "3 notepad restriction"? or it doesn't work ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No restriction found for small files. Maybe the timing is too tense for the "Save As" box to come up in time, and catch all the input, if you have a lot of bigger files to save.

The SendKey approach is unreliable and flaky, so you might have to add more time to the sleep commands. For test, change the two "500" to "5000". If that works, you will have to try some lower values.

Also, another sleep after sending F4 could be necessary.
0
 
ceneiqeAuthor Commented:
Also, another sleep after sending F4 could be necessary.

what do you mean ?
how can this be done ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Well, you really should try to get a grip of what the code does, because YOU will have to adapt it to your environment, and change the delays etc.

All combined, and using very long waits:
cls
Set-StrictMode -version latest

Add-Type -Assembly Microsoft.VisualBasic
Add-Type -Assembly System.Windows.Forms

$folder = read-Host 'Please provide the target folder for unsaved Notepad files:'
$yymm = Get-Date -Format 'yyyy.MM'
$index = dir $folder\$yymm* | sort name -Descending | select -First 1 -ExpandProperty Name
if (!$index) {
  $index = 1
} else {
  $index = [int] $index.Replace('.txt','').Replace($yymm+'-','')+1
}

Get-Process 'notepad*' | 
  ? { $_.MainWindowTitle -eq 'Untitled - Notepad' } |
  sort StartTime | % {
    [Microsoft.VisualBasic.Interaction]::AppActivate($_.ID)
    sleep -m 100
    [System.Windows.Forms.SendKeys]::SendWait('^s')
    sleep -m 5000
    [System.Windows.Forms.SendKeys]::SendWait("$folder\$yymm-$index.txt%s")
    sleep -m 5000
    [System.Windows.Forms.SendKeys]::SendWait("%{F4}")
    sleep -m 5000
    $index++
  }

Open in new window

0
 
ceneiqeAuthor Commented:
Ok i get what you mean, basically is to adjust the numbers accordingly in the following statements:

[Microsoft.VisualBasic.Interaction]::AppActivate($_.ID)
    sleep -m 100
    [System.Windows.Forms.SendKeys]::SendWait('^s')
    sleep -m 5000
    [System.Windows.Forms.SendKeys]::SendWait("$folder\$yymm-$index.txt%s")
    sleep -m 5000
    [System.Windows.Forms.SendKeys]::SendWait("%{F4}")
    sleep -m 5000
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 28
  • 8
  • 7
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now