can I export named ranges into text files with excel ?

We have build documents and want to export names ranges such as:
 interface1
ip
netmask

interface2
ip
netmask

interface
ip
netmask


I am using excel 2010

thx
TIMFOX123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

activematxCommented:
Yes this can be done.  Can you attached a sample file?
0
TIMFOX123Author Commented:
file with 3 named ranges.  I would like each range to be the title of each text file
ee-filewith3namedranges.xlsx
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

The code below I have taken from the "basQ_28239394" code module within the attached workbook.

When the Q_28239394() subroutine code runs (executed by selecting "Q_28239394" from the "Macro" list shown when using the [ALT]+[F8] key combination) it creates text files (with a ".txt" file extension) within the folder "c:\output".

Please ensure this folder exists, or that you change that part of the code to reflect the actual folder required.

Any existing files with the same filename (with a ".txt" file extension) as the first cell within the named ranges will be overwritten when the code runs.

Option Explicit
Public Sub Q_28239394()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28239394.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28239394
' Question Title:   can I export named ranges into text files with excel ?
' Question Asker:   TIMFOX123                                 [ http://www.experts-exchange.com/M_1579133.html ]
' Question Dated:   2013-09-14 at 01:00:59
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim lngErr_Number                                     As Long
  Dim objCell                                           As Range
  Dim objName                                           As Name
  Dim objScripting_FileSystemObject                     As Object
  Dim objTextFile                                       As Object
  Dim strErr_Description                                As String
  
  On Error GoTo Err_Q_28239394
  
  Set objScripting_FileSystemObject = CreateObject("Scripting.FileSystemObject")
  
  For Each objName In ThisWorkbook.Names
      
      Set objTextFile = objScripting_FileSystemObject.CreateTextFile("c:\output\" & _
                                                                     Trim$(objName.RefersToRange.Cells(1&)) & ".txt", True)
      
      For Each objCell In objName.RefersToRange
      
          objTextFile.WriteLine (objCell)
          
      Next objCell
      
      objTextFile.Close
      
      Set objTextFile = Nothing
      
  Next objName
  
Exit_Q_28239394:

  On Error Resume Next
  
  Set objCell = Nothing
  Set objName = Nothing
  
  If Not (objTextFile Is Nothing) Then
     objTextFile.Close
     Set objTextFile = Nothing
  End If ' If Not (objTextFile Is Nothing) Then
  
  Set objScripting_FileSystemObject = Nothing
  
  Exit Sub

Err_Q_28239394:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
         
  Resume Exit_Q_28239394
  
End Sub

Open in new window



Thank you for your feedback when you have had the opportunity to review the results.

BFN,

fp.
Q-28239394.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

TIMFOX123Author Commented:
I am at absolute awe on this solution,  Great Great Great !!!

I am going to award you the points however I have one more optional option.

every time there is a space in my file, it starts a new line

in other words
Hello Word

becomes:
hello
world

Is there a way to keep the lines as they were ?

If it needs to be a csv or what ever that is fine.  This  will work as it is but options are great also

thx again.
0
[ fanpages ]IT Services ConsultantCommented:
Sorry, I don't think I fully understand the issue.

Do you mean that "Hello" & "World" are in two separate cells within a range, or that a single cell within one of the ranges contains the text value of "Hello World" (without the quotes, but with the space character between them)?

If the latter case ("Hello World"), then I am not seeing that with the test data.

For example, these three cell values in a single column:
---
interface3
ip 30
netmask 240
---

Appear in the "output" folder as expected.


Please can you clarify what you mean?  Perhaps attaching an example workbook to demonstrate the problem would be useful.

Thank you.
0
TIMFOX123Author Commented:
it will work as done,  
thx

accepting solution, it is a very good one
0
TIMFOX123Author Commented:
A as in Awsome !!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.