[RESOLVED] want to create a txt file using content from excel

[cssriraman]

Hi all,

I have an excel file. It has some names in Cell A1 to A10 as follows:

          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.

[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

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

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

[cssriraman]

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

[si_the_geek]

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.

[cssriraman]

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.

[ku5e]

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.

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.

[lamensterms]

Thanks a lot for sharing that code westconn1, it has come in very handy.

[Emeroogork]

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

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

[westconn1]

the code can be place anywhere within an existing procedure (macro), or in a procedure in its own

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

[Emeroogork]

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.

[westconn1]

try like

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

[Emeroogork]

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.

[westconn1]

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

[vbfbryce]

http://blog.contextures.com/archives...-on-excel-qat/

[Emeroogork]

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.

[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

[Emeroogork]

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.