Macro for creating text file and copy paste date Closed

Question

Hello experts,

I need an another macro, a keypad shortcut triggered macro (Ctrl+j), which will copy ranges "A10:A45", create a new text file on the desktop, paste the ranges, save the file taking ranges A1 &A2 as the file name. A2 will contain this formula "=now()"

I appreciate the help provided for this. Can you please help me for this macro? 

Thanks and regards,
gm2612

ac3mark · Answer

OK, hang in there....

So what part do yu need help with?  The macro? The formulas, or the creation of the text file?  Where are you getting stuck at?  I can help, but show me what you have completed so that I am not duplicating your work.

Btw, I will not write a complete solution for you, only a wireframe.

Post back

gm2612 · Answer

Hello ac3mark,

Thanks for your reply. I earlier tried in some forums and customized macro, but failed for this function.

I need macro for the above said function. Can you please provide me the same? Function is here again-


Triggered by Ctrl+j

Copy ranges A10:A45 from sheet1
Create a new wordpad file on desktop
Paste the copied ranges as values
Save the wordpad file with the file name taken from range A1&A2 of excel file sheet1 (Here the cell A2 contains the formula "=now()"

Can you please provide me the macro for the same?

THanks and regards,
gm2612

ac3mark · Answer

Ok, Hang in there.

The first thing that I can see is you are going to have to change the format of the NOW() formula, as NOW formats the Date into mm/dd/year, which is a relative path for a file, as the "/" delineates a file path.  So, the first thing, is change the cell that has the date into:
=TEXT(NOW(),"YYYYMMDD")

now your date is converted into a variable that contains no"/".

So start with that, and we will go from there.
 
//ark
-Moderator/Contributor

ac3mark · Answer

OK.

SO you will need to change this to fit your application, as I will only wireframe.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+j
'
    'allocate memory for variables

    Dim ItemName
    Dim stufftocut As Variant
    Dim nameoffileA, nameoffileB
    Dim fso As Object
    Dim oFile As Object
    Dim Sheetname
    Dim therange
    Dim c

    'establish values for variables

    Set Sheetname="somesheetname"
    Set therange="B10:B45"

    Set nameoffileA = Sheets(Sheetname)Cells(1, 1)
    Set nameoffileB = Sheets(Sheetname).Cells(2, 1)
    'get the data from the selection range
    Set stufftocut = Sheets(Sheetname).Range(therange)
    'go through each cell in the range and concate to Itemname, with a carriage return at the end of the line, if you wish to have
    'all of the data on the first line in your file, then remove
    'the vbCrLf (VB designation for Carriage return line feed
    For Each c In stufftocut 
    ItemName = ItemName + c.Text + vbCrLf
    Next c
    'itemname is now a string of all the data
    'now create the file object for writing        
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFile = fso.CreateTextFile("C:\users\owner\desktop\" + CStr(nameoffileA) + CStr(nameoffileB) + ".txt")
        
        oFile.WriteLine CStr(ItemName)
        oFile.Close
        'alwasys close the file or it will not exist
        'ALWAYS DESTROY YOUR OBJECTS!
        Set fso = Nothing
        Set oFile = Nothing
    End Sub
 
 

So, there you have it......
It is not the cleanest, and it performs no error checking. If your file path is different than the one that is posted, make the changes to fit your application.  I wil not get involved with users profiles and such.  I hope that different users on different workstations are not going to use this, because it is not relative path, but absolute.  If you must have different users, then your desktop will need to altered.  Perhaps mapping the users drive to a static z: drive with the destination in a server folder that has those users with permissions to write to it.

Have Fun!

//ark
-Moderator/Contributor

gm2612 · Answer

Thank you ac3mark. I will build it further.

Regards,
gm2612

Macro for creating text file and copy paste date

5 responses

Similar discussions

Subscribe To Our Newsletter!