Export unlocked cells to file

[dan dungan]

Hi,

Using Excel 2000 and Windows XP, I'm attempting to export all the
values in unlocked cells on the active sheet to a text file on my c:
drive.

Here's my problem:

I'm getting all the locked cell values instead of the unlocked cell
values.

In a module I have the two subs below.

Any ideas what I'm doing wrong?

Sub DoExportUnlocked()
ExportUnlocked Fname:="K:\Customer Service\Quote\Details\" & User
& "data.txt", Sep:="|", _
SelectionOnly:=True, AppendData:=True
End Sub

Public Sub ExportUnlocked(Fname As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)

Dim c As Range
Dim rng2 As Range
Dim FNum As Integer
Dim WholeLine As String
FNum = FreeFile

For Each c In ActiveSheet.UsedRange
If Not (c.Locked) Then
If Not rng2 Is Nothing Then
Set rng2 = Union(c, rng2)
Else
Set rng2 = c
End If
End If
WholeLine = WholeLine & c & Sep
Next c
Open Fname For Append Access Write As #FNum
'rng2.Select
Print #FNum, WholeLine
On Error GoTo 0
Close #FNum

End Sub

 

[Dave Peterson]

This portion builds a range based on the lockedness of a cell:

For Each c In ActiveSheet.UsedRange
If Not (c.Locked) Then
If Not rng2 Is Nothing Then
Set rng2 = Union(c, rng2)
Else
Set rng2 = c
End If
End If
WholeLine = WholeLine & c & Sep
Next c

But wholeline is updated no matter if C is locked or unlocked.

Maybe just...

For Each c In ActiveSheet.UsedRange
If c.Locked = false Then
WholeLine = WholeLine & c & Sep
end if
Next c

I find
if c.locked = false then
easier to read than
if not (c.locked) then

But it's a personal choice.

And I would have written this:

If Not rng2 Is Nothing Then
Set rng2 = Union(c, rng2)
Else
Set rng2 = c
End If

as

If rng2 Is Nothing Then
Set rng2 = c
Else
Set rng2 = union(c, rng2)
End If

Too many negatives can confuse my brain <vbg>.

 

[dan dungan]

Thanks Dave,

I'll try this out, and let you know.

Thanks again,

Dan

 

[dan dungan]

Hi Dave,

This worked great!

Thank you very much.

Dan

 

[Dave Peterson]

Glad you got it working!

Hi Dave,

This worked great!

Thank you very much.

Dan