Macro for creating text file and copy paste date

[Closed]
Report
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
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

5 replies


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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

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

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

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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

Thank you ac3mark. I will build it further.

Regards,
gm2612