can I export named ranges into text files with excel ?

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



I am using excel 2010

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.

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

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()

' --------------------------------------------------------------------------------------------------------------
' [ ]
' 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                                 [ ]
' Question Dated:   2013-09-14 at 01:00:59
' Expert Comment:   fanpages                                   [ ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ ]
' --------------------------------------------------------------------------------------------------------------

  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
      Set objTextFile = Nothing
  Next objName

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


  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  On Error Resume Next
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
  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.



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


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.
[ 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:
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.
TIMFOX123Author Commented:
it will work as done,  

accepting solution, it is a very good one
TIMFOX123Author Commented:
A as in Awsome !!!
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.