Export unlocked cells to file
 

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

Export unlocked cells to file
 

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 wrote:

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

Export unlocked cells to file
 

Thanks Dave,

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

Thanks again,

Dan

Export unlocked cells to file
 

Hi Dave,

This worked great!

Thank you very much.

Dan

Export unlocked cells to file
 

Glad you got it working!

dan dungan wrote:

Hi Dave,

This worked great!

Thank you very much.

Dan

--

Dave Peterson