Macro for creating text file and copy paste date
Closed
gm2612
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
Dec 30, 2013 at 07:07 AM
gm2612 Posts 12 Registration date Monday December 23, 2013 Status Member Last seen January 1, 2014 - Jan 1, 2014 at 02:39 AM
gm2612 Posts 12 Registration date Monday December 23, 2013 Status Member Last seen January 1, 2014 - Jan 1, 2014 at 02:39 AM
Related:
- Macro for creating text file and copy paste date
- Windows 10 iso file download 64-bit - Download - Windows
- Gta 5 exe file download for pc - Download - Action and adventure
- Tiny 11 iso file download - Download - Windows
- Kmspico zip file download - Download - Other
- Ms office save as pdf or xps file - Download - Other
5 responses
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
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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Dec 30, 2013 at 11:02 PM
Dec 30, 2013 at 11:02 PM
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
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
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
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
Didn't find the answer you are looking for?
Ask a question
gm2612
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Jan 1, 2014 at 02:39 AM
Jan 1, 2014 at 02:39 AM
Thank you ac3mark. I will build it further.
Regards,
gm2612
Regards,
gm2612
