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

[vishp127]

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..

 

[vishp127]

Hi Logit

Thanks for your reply. When I pressed F5, a window pops up asking to create Macro name. If I name Macro as Worksheet_Change(ByVal Target As Range), the error pops up saying Invalid procedure name. If I name Macro as Worksheet_Change, the error pops up saying Compile error: Variable not defined.

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.

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([B]Target[/B].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

 

[Logit]

I wish this Forum would allow the attachment of files. That would allow the viewers to study the code first hand within the file to see how it is setup and functions.

In any case, the code should be placed in the VBE editor, in the Sheet1(Sheet1) module. Since it is functioning as a 'Worksheet_Change(ByVal Target As Range)'
event it must be pasted in that location.

Should you want to use this snippet for another Sheet, you can paste the code in the appropriate Worksheet module.

The macro does not need to be run by pressing F5 or by way of a CommandButton. The macro works when the contents of R2 changes. To test the code,
enter something into cell R2.

The line adjacent to 'Option Explicit' that reads : "'Creates file if contents of cell B1 changes" should read Creates file if contents of cell R2 changes. Sorry for the confusion. I was testing the code on
​another cell prior to posting.

If interested, you can download the project here: https://www.amazon.com/clouddrive/s...zbhWdqxpXJP6a3gqlO?ref_=cd_ph_share_link_copy

 

[vishp127]

Hi Logit

Thanks for reply. Your code works perfect when the target cell is not a result of formula. I found below code on internet as a workaround.

Sub Range_PasteSpecial_Values()
Range("R2").Copy
Range("R3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

When I run above macro manually, it copies the values from R2 to R3, followed by your code is also creating text file successfully. Now to automate this process I tried to merge above and below code into your code, which did not work out very well by me.

example excel file is available for download: 9. VBA Tips - Run Code Every Hour, Minute or Second | ExcelExperts.com

Please have a look and see if you can merge all those codes.

I wish this Forum would allow the attachment of files. That would allow the viewers to study the code first hand within the file to see how it is setup and functions.

In any case, the code should be placed in the VBE editor, in the Sheet1(Sheet1) module. Since it is functioning as a 'Worksheet_Change(ByVal Target As Range)'
event it must be pasted in that location.

Should you want to use this snippet for another Sheet, you can paste the code in the appropriate Worksheet module.

The macro does not need to be run by pressing F5 or by way of a CommandButton. The macro works when the contents of R2 changes. To test the code,
enter something into cell R2.

The line adjacent to 'Option Explicit' that reads : "'Creates file if contents of cell B1 changes" should read Creates file if contents of cell R2 changes. Sorry for the confusion. I was testing the code on
​another cell prior to posting.

If interested, you can download the project here: https://www.amazon.com/clouddrive/s...zbhWdqxpXJP6a3gqlO?ref_=cd_ph_share_link_copy

 

[Logit]

Here is the code combined:

Option Explicit

'Creates file if R2 on the sheet changes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim fs As Object
Dim a As Object
     
    'Copy contents of R2
    Range("R2").Copy
    
    'Paste contents from R2 into R3
    Range("R3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Create object to create the text file funtions
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    'Creates a text file with the name of the contents from cell R3
    Set a = fs.CreateTextFile("c:\Users\My\Desktop\" & Range("R3").Value & ".")  '<-- Change as needed
    
    'Close the text file
    a.Close
    
'Set everything equal to nothing in case a new text file is to be created
Set a = Nothing
Set fs = Nothing


End Sub

I looked at the website link but am confused if there was something else you were needing. If so, please explain in more detail.