Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export unlocked cells to file
Thanks Dave,
I'll try this out, and let you know. Thanks again, Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export unlocked cells to file
Hi Dave,
This worked great! Thank you very much. Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to export unlocked cells to a row in a text file. | Excel Programming | |||
How to export a range of cells on a worksheet to a text file | Excel Programming | |||
Macro to Export Active Cells, Comma Sep to .txt file | Excel Programming | |||
Moving from unlocked cells to unlocked cells in an excel form | Excel Worksheet Functions | |||
Why can't I Export selected cells to tab-delimited text file? | Excel Discussion (Misc queries) |