-
Jun 25th, 2005, 06:28 AM
#1
Thread Starter
Frenzied Member
[RESOLVED] want to create a txt file using content from excel
Hi all,
I have an excel file. It has some names in Cell A1 to A10 as follows:
Code:
A
1 app-01
2 app-03
3 app-05
4 app-07
5 app-09
6 app-11
7 app-13
8 app-15
9 app-17
10 app-19
The user will select the cells A1 to A10 (or A1 to A5 as per users wish) and then, if we run a macro, the macro should create a text files from A1 to A10 (or as per users wish), like "app-01.txt", "app-03.txt", "app-05.txt" and so on in "C:\Temp\" folder.
Any Coding help pls!
Thanks,
CS.
Last edited by cssriraman; Jun 25th, 2005 at 11:07 AM.
-
Jun 25th, 2005, 10:11 AM
#2
Re: want to create a txt file using content from excel
not quite sure what you wanted, but here is 2 choices, 1st puts values from cells in a column into a text file, 2nd creates empty text file for each cell with value in the selected ist column, blanks omitted, duplicates overwritten
VB Code:
mypathname = "C:\temp\" & "qwerty.txt"
f = FreeFile
Open mypathname For Output As f
For i = 1 To Selection.Rows.Count
Print #f, Selection.Cells(i, 1).Text
Next
Close f
VB Code:
mypath = "C:\temp\"
For i = 1 To Selection.Rows.Count
mytext = Selection.Cells(i, 1).Text
If Not Len(mytext) = 0 Then
f = FreeFile
Open mypath & mytext & ".txt" For Output As f
Close f
End If
Next
pete
-
Jun 25th, 2005, 10:21 AM
#3
Thread Starter
Frenzied Member
Re: want to create a txt file using content from excel
Thanks, I want to create a empty txt file. I will be working with MS Excel only. the code you mentioned will not work with MS Excel. I think it will work with MS Word. Pls help me. CS
-
Jun 25th, 2005, 10:46 AM
#4
Re: want to create a txt file using content from excel
Westconn1's code works fine for me in Excel.
One possible issue tho is that the folder (in this case "C:\temp\") needs to exist first.
-
Jun 25th, 2005, 11:06 AM
#5
Thread Starter
Frenzied Member
Re: want to create a txt file using content from excel
I am sorry. Westconn1's code works fine. The folder "C:\Temp\" is not exist. That is the reason it was not worked previously.
Thank you so much guys.
CS.
-
Sep 27th, 2012, 02:37 PM
#6
New Member
Re: [RESOLVED] want to create a txt file using content from excel
With your help i made over 140 301 redirects for a site that was consolidating pages in less than 2 seconds
I prepped all the file names in one column and created the links in another with concatenate, then I used a combo of the code above to create the files and populate each one of them with the php code for redirection. (don't even ask why .htaccess was not an option, grrrrrr). Here is the macro code as I used it.
Code:
Sub createfiles()
'
' createfiles Macro
'
mypath = "C:\temp\"
For i = 1 To Selection.Rows.Count
mytext = Selection.Cells(i, 1).Text
If Not Len(mytext) = 0 Then
f = FreeFile
Open mypath & mytext For Output As f
Print #f, Selection.Cells(i, 2).Text
Close f
End If
Next
'
End Sub
Thanks for the assist.
-
Apr 15th, 2014, 10:14 PM
#7
New Member
Re: [RESOLVED] want to create a txt file using content from excel
Thanks a lot for sharing that code westconn1, it has come in very handy.
-
Jun 26th, 2014, 11:52 PM
#8
Lively Member
Re: want to create a txt file using content from excel
Originally Posted by westconn1
not quite sure what you wanted, but here is 2 choices, 1st puts values from cells in a column into a text file, 2nd creates empty text file for each cell with value in the selected ist column, blanks omitted, duplicates overwritten
VB Code:
mypath = "C:\temp\" For i = 1 To Selection.Rows.Count mytext = Selection.Cells(i, 1).Text If Not Len(mytext) = 0 Then f = FreeFile Open mypath & mytext & ".txt" For Output As f Close f End If Next
pete
I am new to macros.
It looks as my attempt to run this isn't going to work as I placed it as VB code in "Microsoft Excel Object, Sheet1 (Sheet1)"
I am going to guess that has to be coded within a macro and they may explain why I cannot invoke it.
I would appreciate it if you could enlighten me in how to do this that I may see the light....
-
Jun 27th, 2014, 03:22 AM
#9
Re: [RESOLVED] want to create a txt file using content from excel
the code can be place anywhere within an existing procedure (macro), or in a procedure in its own
Code:
sub dosomestuff()
mypath = "C:\temp\"
For i = 1 To Selection.Rows.Count
mytext = Selection.Cells(i, 1).Text
If Not Len(mytext) = 0 Then
f = FreeFile
Open mypath & mytext & ".txt" For Output As f
Close f
End If
Next
end sub
you may use some button on the ribbon or toolbar to run the code, or you can just use the run button in the visual basic IDE
or it can be an event procedure for some action of the user, depending on your requirements
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 27th, 2014, 11:09 PM
#10
Lively Member
Re: [RESOLVED] want to create a txt file using content from excel
Ok, It has taken me a but to get back to this project but here I am.
It is working.
Now, how do I add some content to these files?
Let's presume that there are readings in the columns immediately to the right of each cell in the selected group.
How would I see that information and once I have it, how is the line written to place said information within the file?
Also, You seem to mention that I can have a button in the ribbon to activate the writing of files. How is that done?
I know how to do the last two activations but not that first one.
Last edited by Emeroogork; Jun 27th, 2014 at 11:13 PM.
-
Jun 28th, 2014, 05:22 PM
#11
Re: [RESOLVED] want to create a txt file using content from excel
try like
Code:
sub dosomestuff()
mypath = "C:\temp\"
For i = 1 To Selection.Rows.Count
mytext = Selection.Cells(i, 1).Text
If Not Len(mytext) = 0 Then
f = FreeFile
Open mypath & mytext & ".txt" For Output As f
print #f, selection.cells(i, 1).offset(, 1) ' one column to right
Close f
End If
Next
end sub
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 28th, 2014, 07:09 PM
#12
Lively Member
Re: [RESOLVED] want to create a txt file using content from excel
Yes it works well. I expanded it using a loop to print a few columns for each row.
Now, back to my other question. Do I understand correctly that one can set a button in the ribbon to activate this? It is a bit clumsy to have to go into the VB screen to hit run.
-
Jun 29th, 2014, 03:23 AM
#13
Re: [RESOLVED] want to create a txt file using content from excel
Do I understand correctly that one can set a button in the ribbon to activate this?
yes you can do that, i do not have ribbon, so can not help with that
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 29th, 2014, 06:55 AM
#14
Re: [RESOLVED] want to create a txt file using content from excel
-
Jun 29th, 2014, 08:33 AM
#15
Lively Member
Re: [RESOLVED] want to create a txt file using content from excel
I will look at that blog entry again but I stumble when acronyms are used and not defined.
It prevents me from being able to follow the instructions.
What is a QAT? The lack of foresight to explain the term is annoying to say the least.
-
Jun 29th, 2014, 03:57 PM
#16
Re: [RESOLVED] want to create a txt file using content from excel
try
right click on ribbon > customise > commands > macros
maybe a bit different, as this is for a toolbar button, without ribbon
you can save time by putting your favourite commands on the Quick Access Toolbar (QAT).
right at the top of the blog
Last edited by westconn1; Jun 29th, 2014 at 04:01 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 29th, 2014, 05:30 PM
#17
Lively Member
Re: [RESOLVED] want to create a txt file using content from excel
Originally Posted by westconn1
try
right click on ribbon > customise > commands > macros
maybe a bit different, as this is for a toolbar button, without ribbon
"you can save time by putting your favourite commands on the Quick Access Toolbar (QAT)."
right at the top of the blog
Right you are... It is interesting that, with all the pointless URL references rampant all over the internet, I no longer see them as with so many pointless ads. I did over look that one.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|