Create text file with file type ALL FILES even if excel is minimized

vishp127

New Member
Joined
Jul 4, 2016
Messages
8
Hi Guys

I am new to excel vba and can't make this work.. This is scenario, the value in R2 cell changes at anytime during day and has just changed to AB12CD34EF567. Now I want excel to create a text file name with this cell value in C:\Users\Dell\Downloads.

The catch here is I want this file to be created without *.txt extension. For example, If manually I am creating this file then the procedure would be clicking FILE menu from text file, select SAVE AS option, Give file name as AB12CD34EF567. (notice there is a (.) dot, at the end of file name), select SAVE AS TYPE to ALL FILES, and save the file name.

I do not want any manual intervention in above procedure.. :confused:
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
When you say "changes at anytime during the day", are you wanting to capture this change once .... or multiple times throughout the day ?

I have a sample file that will accomplish your goal for once a day. Someone would need to check the file periodically if you wanted it to repeat the process
without over writing the file. Or - the file will give you a start toward something more involved.
 
Upvote 0
I don't have the "All Files" option when saving files only when opening files. What version of Excel do you use?
 
Upvote 0
When you say "changes at anytime during the day", are you wanting to capture this change once .... or multiple times throughout the day ?

I have a sample file that will accomplish your goal for once a day. Someone would need to check the file periodically if you wanted it to repeat the process
without over writing the file. Or - the file will give you a start toward something more involved.

Hi Logit

Thanks for your reply. I will try to explain again..
The cell contents of R2 is a result of this formula =INDEX($C$2:$C$16,MATCH(SMALL($O$2:$O$16,P2),$O$2:$O$16,0)).. All those cell values in the formula changes every single second throughout the day. For example, the current content of R2 is AB12CD34EF567. Now excel will re-write this values in R2 every second. But at some point of time it will change to, for example, XYZ123ABC890A. Once this new value gets detected by excel, I want a file to be created with this new name at the same time. So, here we have same value flashing every second in a cell, but the file needed to be created when new values (other than current values) detected by excel. Now About over-writing, the file name carries message itself so, contents are not necessary.
 
Upvote 0
I don't think that "All Files" is an option for saving files.
See:
http://www.mrexcel.com/forum/excel-questions/35826-saving-workbook-without-extension.html
The following bit of code is an example of "creating" a file without an extension:
Code:
Option Explicit
Sub CreateAfile()
Dim fs As Object
Dim a As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\Excel Test\testfile.", True)
    a.Close
Set a = Nothing
Set fs = Nothing
End Sub
You may be able to adapt that to pick up the required name.
 
Last edited:
Upvote 0
Using this code in the Sheet module:

Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fs As Object
Dim a As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\Users\My\Desktop\" & Range("R2").Value & ".")  '<-- Change as needed
    a.Close
Set a = Nothing
Set fs = Nothing
End Sub
 
Upvote 0
I don't think that "All Files" is an option for saving files.
See:
http://www.mrexcel.com/forum/excel-questions/35826-saving-workbook-without-extension.html
The following bit of code is an example of "creating" a file without an extension:
Code:
Option Explicit
Sub CreateAfile()
Dim fs As Object
Dim a As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\Excel Test\testfile.", True)
    a.Close
Set a = Nothing
Set fs = Nothing
End Sub
You may be able to adapt that to pick up the required name.

Hi Derek

Thanks for reply.. I tried putting that code in Module under Sheet1, then pressed F5 (Play button just below Debug menu), it pops up Macro window asking to create new Macro.. I am very much new to this VB Macro thing.. I am explaining in a brief about my requirement..

The cell contents of R2 is a result of this formula =INDEX($C$2:$C$16,MATCH(SMALL($O$2:$O$16,P2),$O$2:$O$16,0)).. All those cell values (data range) in the formula changes every single second throughout the day. For example, the current content of R2 is ABC123. Now excel will re-write this content in R2 every single second. until the new content gets calculated by above formula. for example, after two minutes it has changed to XYZ890. Once this new content gets detected by excel, I want a file to be created with this new content at the same time.

So, we have same content flashing every second in a R2, but the file needed to be created only when new content (other than current content) gets detected by excel. Now About over-writing, the file name carries message itself so, contents are not necessary.
I was talking about "All Files" when saving text file.
 
Upvote 0
Actually, a better way of accomplishing your goal is with this macro. It only creates the file when just the contents of R2 changes.
The first macro creates a file when any cell on the sheet changes.

Code:
Option Explicit'Creates file if contents of cell B1 changes


Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim fs As Object
    Dim a As Object
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("R2")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile("c:\Users\My\Desktop\" & Range("R2").Value & ".")  '<-- Change as needed
        a.Close
        Set a = Nothing
        Set fs = Nothing
       
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top