create text file from a mixture of cells value is Excel and text.

S

scorney

New Member
Joined
Jun 5, 2012
Messages
4
I’m looking into creating a macro or something that would allow me to take info from specific cells and add it to a text file which the result has to look like as below:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
epipe 12193934 customer 2 create<o:p></o:p>
description "RS19-BVA0277-Telecom-3934"<o:p></o:p>
endpoint Epipe_Telecom_3934 create <o:p></o:p>
revert-time 90 <o:p></o:p>
exit <o:p></o:p>
sap 1/1/5:3934 create <o:p></o:p>
exit <o:p></o:p>
spoke-sdp 1119:12193934 endpoint Epipe_Telecom_3934 create<o:p></o:p>
precedence primary <o:p></o:p>
exit <o:p></o:p>
spoke-sdp 1219:12193912 endpoint Epipe_Telecom_3934 create<o:p></o:p>
exit <o:p></o:p>
no shutdown <o:p></o:p>
exit<o:p></o:p>
<o:p> </o:p>
All the value ='All RS 7705-Service Epipe'!x190 are from Worksheet ALL RS 7705-Service Epipe cell x, I’m having a few hundred row, but all info for a specific epipe are on the same line, in this example its all on row 190, but different column.
Below example show the pattern:
<o:p> </o:p>
epipe ='All RS 7705-Service Epipe'!C190 customer 2 create<o:p></o:p>
description "='All RS 7705-Service Epipe'!E190"<o:p></o:p>
endpoint ='All RS 7705-Service Epipe'!F190 create <o:p></o:p>
revert-time 90 <o:p></o:p>
exit <o:p></o:p>
sap ='All RS 7705-Service Epipe'!H190 create <o:p></o:p>
exit <o:p></o:p>
spoke-sdp ='All RS 7705-Service Epipe'!I190 endpoint ='All RS 7705-Service Epipe'!F190 create<o:p></o:p>
precedence primary <o:p></o:p>
exit <o:p></o:p>
spoke-sdp ='All RS 7705-Service Epipe'!K190 endpoint ='All RS 7705-Service Epipe'!F190 create<o:p></o:p>
exit <o:p></o:p>
no shutdown <o:p></o:p>
exit<o:p></o:p>
<o:p> </o:p>
or
<o:p> </o:p>
Can I build something that would set a variable and pull the info from a worksheet, but I just don’t know how to do and where to start?
<o:p> </o:p>
epipe $A customer 2 create<o:p></o:p>
description "$B"<o:p></o:p>
endpoint $C create <o:p></o:p>
revert-time 90 <o:p></o:p>
exit <o:p></o:p>
sap $D create <o:p></o:p>
exit <o:p></o:p>
spoke-sdp $E endpoint $F create<o:p></o:p>
precedence primary <o:p></o:p>
exit <o:p></o:p>
spoke-sdp $G endpoint $F create<o:p></o:p>
exit <o:p></o:p>
no shutdown <o:p></o:p>
exit<o:p></o:p>
<o:p> </o:p>
$A='All RS 7705-Service Epipe'!C190<o:p></o:p>
$B='All RS 7705-Service Epipe'!E190<o:p></o:p>
$C='All RS 7705-Service Epipe'!F190<o:p></o:p>
$D='All RS 7705-Service Epipe'!H190<o:p></o:p>
$E='All RS 7705-Service Epipe'!I190<o:p></o:p>
$F='All RS 7705-Service Epipe'!F190<o:p></o:p>
$G='All RS 7705-Service Epipe'!K190<o:p></o:p>
<o:p> </o:p>
Not sure what is the best way to built this.<o:p></o:p>
Can it be built just using a macro in Excel or using MS Word and pulling nfo from Excel?

Thanks for your support!
Sylvain
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Hi and welcome to the forum.

The way I have approached your problem is to loop through the data, build up a text string and output it to a text file.

For ease of editing I have used two constants, one for the sheet name, and one for the output file.

Code: 
[COLOR=darkblue]Const[/COLOR] sheetName = "ALL RS 7705-Service Epipe"
[COLOR=darkblue]Const[/COLOR] sFile = "C:\scorney.txt"

If we open the output text file using the OUTPUT command a new text file is created each time the code is run.
Code: 
   [COLOR=darkblue]Open[/COLOR] sFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Output[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum

If you wish to add the output to an existing file use the APPEND command
Code: 
   [COLOR=darkblue]Open[/COLOR] sFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Append[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum

We set up a range to loop through column A until we find a cell with no value:
Code: 
   [COLOR=darkblue]Set[/COLOR] rng = Sheets(sheetName).Range("A2")
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""

Then we build up our output string:
Code: 
      [COLOR=green]'build output string[/COLOR]
      sOutput = "epipe " & rng.Offset(, 2).Value & " customer 2 create" & vbCrLf _
            & "description " & rng.Offset(, 4).Value & vbCrLf _
            & "endpoint " & rng.Offset(, 5).Value & " create" & vbCrLf _
            & "revert-time 90" & vbCrLf _
            & "exit" & vbCrLf _
            & "sap " & rng.Offset(, 7).Value & " create" & vbCrLf _
            & "exit" & vbCrLf _
            & "spoke-sdp " & rng.Offset(, 8).Value & " endpoint " & rng.Offset(, 5).Value & " create" & vbCrLf _
            & "precedence primary " & vbCrLf _
            & "exit" & vbCrLf _
            & "spoke-sdp " & rng.Offset(, 10).Value & " endpoint " & rng.Offset(, 5).Value & " create" & vbCrLf _
            & "exit" & vbCrLf _
            & "no shutdown" & vbCrLf _
            & "exit" & vbCrLf

And output it to the text file:
Code: 
      [COLOR=green]'output[/COLOR]
      [COLOR=darkblue]Print[/COLOR] #fNum, sOutput

Then we get the next row of data and do it all again.

To use.
Create a copy of your workbook.
Press Alt+F11 to open the vba editor window.
Double click o the ThisWorkbook module in the Project Window on the left hand side.
Copy and paste the code below.
Press F5 to run.
Check the output file.
Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Const[/COLOR] sheetName = "ALL RS 7705-Service Epipe"
[COLOR=darkblue]Const[/COLOR] sFile = "C:\scorney.txt"
 
[COLOR=darkblue]Sub[/COLOR] ExportToTextFile()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range  [COLOR=green]'data range to loop through[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sOutput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] fNum [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] CloseTextFile
   fNum = FreeFile
   [COLOR=darkblue]Open[/COLOR] sFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Output[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum
 
   [COLOR=darkblue]Set[/COLOR] rng = Sheets(sheetName).Range("A2")
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
 
      [COLOR=green]'build output string[/COLOR]
      sOutput = "epipe " & rng.Offset(, 2).Value & " customer 2 create" & vbCrLf _
            & "description " & rng.Offset(, 4).Value & vbCrLf _
            & "endpoint " & rng.Offset(, 5).Value & " create" & vbCrLf _
            & "revert-time 90" & vbCrLf _
            & "exit" & vbCrLf _
            & "sap " & rng.Offset(, 7).Value & " create" & vbCrLf _
            & "exit" & vbCrLf _
            & "spoke-sdp " & rng.Offset(, 8).Value & " endpoint " & rng.Offset(, 5).Value & " create" & vbCrLf _
            & "precedence primary " & vbCrLf _
            & "exit" & vbCrLf _
            & "spoke-sdp " & rng.Offset(, 10).Value & " endpoint " & rng.Offset(, 5).Value & " create" & vbCrLf _
            & "exit" & vbCrLf _
            & "no shutdown" & vbCrLf _
            & "exit" & vbCrLf
 
      [COLOR=green]'output[/COLOR]
      [COLOR=darkblue]Print[/COLOR] #fNum, sOutput
 
      [COLOR=green]'next row[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
CloseTextFile:
   Close #fNum
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Hi Bertie,

You are a life saver!
I tried it and played with offsets and your explanation were very good

I will for sure apply this to other script that I have to built!

Thanks a bunch for your help!

Best Regards,
Sylvain
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,219,162
Messages
6,146,659
Members
450,706
Latest member
LGVBPP

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top