Solved

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

Posted on 2013-11-11
51
385 Views
Last Modified: 2014-03-03
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
Comment
Question by:ceneiqe
  • 28
  • 8
  • 7
  • +5
51 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39640594
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39640680
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39640920
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
 

Author Comment

by:ceneiqe
ID: 39643495
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39646554
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
 

Author Comment

by:ceneiqe
ID: 39658251
Hi could you kindly paste the script where i can it from Excel ?

Thanks.
0
 

Author Comment

by:ceneiqe
ID: 39658254
i am putting all the macros in excel so that i can just ALT+F8  to run the scripts.
0
 

Author Comment

by:ceneiqe
ID: 39661651
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
 
LVL 59

Expert Comment

by:LeeTutor
ID: 39720087
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
 

Author Comment

by:ceneiqe
ID: 39720086
i believe my questions are clear enough for comments or answers.
0
 

Author Comment

by:ceneiqe
ID: 39720088
i believe my questions are clear enough for comments or answers.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39721557
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 39721606
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
 

Author Comment

by:ceneiqe
ID: 39726778
ok let me try out and see if it works. thks
0
 

Author Comment

by:ceneiqe
ID: 39764393
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
 

Author Comment

by:ceneiqe
ID: 39764401
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
 

Author Comment

by:ceneiqe
ID: 39764404
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39764835
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
 

Author Comment

by:ceneiqe
ID: 39767199
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39768178
@ceneiqe

What code was running when you received your scripting host error?
0
 

Author Comment

by:ceneiqe
ID: 39770037
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39771191
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
 

Author Comment

by:ceneiqe
ID: 39774689
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
 

Author Comment

by:ceneiqe
ID: 39774694
I have increased points from 250 to 400.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 45

Expert Comment

by:aikimark
ID: 39774715
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
 

Author Comment

by:ceneiqe
ID: 39788054
for example ?
what format must i adhere?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39788640
strTitle = "This is my title"

Open in new window

0
 

Author Comment

by:ceneiqe
ID: 39809661
and then

strPrompt = ?
strDefault = ?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 400 total points
ID: 39810002
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39810211
@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
 

Author Comment

by:ceneiqe
ID: 39811069
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39811289
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
 

Author Comment

by:ceneiqe
ID: 39814148
also can't access.

Powershell access denied
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39814426
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
 

Author Comment

by:ceneiqe
ID: 39831592
But now nothing happens.

Powershell Force scope- try2
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39831762
The command you show in the screenshot is neither of the commands I did ...
0
 

Author Comment

by:ceneiqe
ID: 39832073
ops sorry i think i missed out "-Scope CurrentUser" will try again..
0
 

Author Comment

by:ceneiqe
ID: 39834671
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
 

Author Comment

by:ceneiqe
ID: 39834673
There were 6 files and only 4 were saved and i was prompted to save the other 2 files.
(see above 2nd image)
0
 

Author Comment

by:ceneiqe
ID: 39834674
I need to save 31 files. (assume there are 31 days in a month and 1 data file each day)
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39835797
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
 

Author Comment

by:ceneiqe
ID: 39837929
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39838228
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
 

Author Comment

by:ceneiqe
ID: 39852432
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
 

Author Comment

by:ceneiqe
ID: 39852433
Have you test the "3 notepad restriction"? or it doesn't work ?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39852625
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
 

Author Comment

by:ceneiqe
ID: 39862829
Also, another sleep after sending F4 could be necessary.

what do you mean ?
how can this be done ?
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
ID: 39862888
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
 

Author Comment

by:ceneiqe
ID: 39866512
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This Micro Tutorial will teach you how to change your appearance and customize your Windows 7 interface to your unique preference. This will be demonstrated using Windows 7 operating system.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now