asked on

Excel Import/Export Named Ranges and Values

Hello Experts,

In the attached file, I have the following code that exports up to 20 named ranges and their corresponding cell values to a Text file and it lists all in separate rows. See result in data file Data.txt:

Sub WriteToTextFile()
    Dim c As Range, r As Range
    Dim output As String
    For Each r In Worksheets("EXPORT").Range("Date, Employee, ProspectClientExisting, EstimatedRevenue, Partner, Manager, BillingManager, ClientName, Address, Address2, Address3, City, State, Zip, Country, ContactName, ContactEmail, ContactTelephone, ContactTelephone2, ContactFax, Attention, Invoice").Rows
        For Each c In r.Cells
            output = output & c.Name.Name & "," & c.Value & ","
        Next c
        output = output & vbNewLine
    Next r
    Open "C:\Code Test\Data.txt" For Output As #1
    Print #1, output
    Close
End Sub

Open in new window

My question is in two parts:

I would like to be able to export all named ranges and their corresponding values to a text file, which can go up to 100 names

The named range values from the text file will then need to be imported into a separate Excel file, which has the same named ranges

The rationale of exporting the named range values into a text file and then importing to a separate file is for separation of duties between departments. I'm using Excel 2013 and Windows 7.

Any help with this will be greatly appreciated.

Thank you,

Sura

WriteToTextFile.xlsm

Ryan Chong

for part 1:

a quick approach:

    Dim ws As Worksheet
    Dim c As Range, r As Range
    Dim lastVal As Range
    Dim output As String
    Dim idx As Integer
    Dim isSkip As Boolean
    
    Set ws = Worksheets("EXPORT")
    
    Set lastVal = ws.Columns("B").Find("*", ws.Cells(1, 2), xlValues, xlPart, xlByColumns, xlPrevious)
                                
    Set r = ws.Range("A2", lastVal).Resize(, 2)
    
    For Each c In r.Cells
        Select Case idx
        Case 0
            If Trim(c.Value) = "" Then
                isSkip = True
            Else
                output = output & Left(c.Value, Len(c.Value) - 1) & ","
            End If
            idx = idx + 1
        Case 1
            If isSkip = True Then
                isSkip = False
            Else
                output = output & c.Value & ","
                output = output & vbNewLine
            End If
            idx = 0
        End Select
    Next c
    
    Open "C:\Code Test\Data.txt" For Output As #1
    Print #1, output
    Close
End Sub

Open in new window

for part 2, it could be a bit more complicated.

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SuraDalbin

ASKER

Ryan, thank you for your help.

Gowflow, your solution works perfectly. Thank you very much!

Jacques Geday

Your most welcome.
gowflow