D
dan dungan
Hi,
I've been searching the archives with "export range", and I either
don't understand what I'm looking at or I need a different solution.
Using Excel 2000 and windows ex, I want to copy all the unlocked
cells--blank or not-- with a "|"delimiter between each cell value all
in one row.
I have a spreadsheet with a command button--Add A New Part--that
validates the data entry, prints the sheet,
and clears the cells.
It's a bunch of code--I'll show it if requested.
I'd like to use the same command button and add a
new procedure that exports all the unlocked cells to a text file after
the validation and before the sheet is printed and cleared.
The user will then fill in the spread sheet again, and I want to
capture that in the second row of the text file.
There is a range between A1: N98.
Several of the cells are unlocked for data entry.
I've been trying to use Chip Pearson's ExportToTextFile procedure--
shown below, but it copies row by row and I don't understand how to
edit it to get what I want.
Here's Chip's code. Can it be revised to meet my objective?
Thanks for any suggestions.
Dan
--------------------------------------------------------------
Exporting To A Text File
This procedure allows you to export data from a worksheet range to a
text file. You may specify the character (e.g, a space, tab, pipe,
comma, etc) that separates the exported elements. Each row of cells in
the worksheet is written to one line within the text file, and each
item in that line is separated by the specified delimiter character.
Any single character may be used as the delimiter.
The ExportToTextFile procedure follows. The parameters to
ExportToTextFile are described in the following table:
Parameter Description
FName The name of the file to which the data will be written. The
file will be created if it does not exist. See AppendData below.
Sep The character that is to separate the elements on each row of the
exported file. Typically, this is vbTab, a space, a comma, semicolor,
or pipe ( | ). Any character may be used.
SelectionOnly If True, only the currently selected cells are
exported. If False, the entire used range of the worksheet is
exported.
AppendData If True and FName exists, data is written to the end of
the text file, preserving the existing contents. If False, the
existing contents of FName are destroyed and only the newly exported
data will appear in the output file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If
If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile
I've been searching the archives with "export range", and I either
don't understand what I'm looking at or I need a different solution.
Using Excel 2000 and windows ex, I want to copy all the unlocked
cells--blank or not-- with a "|"delimiter between each cell value all
in one row.
I have a spreadsheet with a command button--Add A New Part--that
validates the data entry, prints the sheet,
and clears the cells.
It's a bunch of code--I'll show it if requested.
I'd like to use the same command button and add a
new procedure that exports all the unlocked cells to a text file after
the validation and before the sheet is printed and cleared.
The user will then fill in the spread sheet again, and I want to
capture that in the second row of the text file.
There is a range between A1: N98.
Several of the cells are unlocked for data entry.
I've been trying to use Chip Pearson's ExportToTextFile procedure--
shown below, but it copies row by row and I don't understand how to
edit it to get what I want.
Here's Chip's code. Can it be revised to meet my objective?
Thanks for any suggestions.
Dan
--------------------------------------------------------------
Exporting To A Text File
This procedure allows you to export data from a worksheet range to a
text file. You may specify the character (e.g, a space, tab, pipe,
comma, etc) that separates the exported elements. Each row of cells in
the worksheet is written to one line within the text file, and each
item in that line is separated by the specified delimiter character.
Any single character may be used as the delimiter.
The ExportToTextFile procedure follows. The parameters to
ExportToTextFile are described in the following table:
Parameter Description
FName The name of the file to which the data will be written. The
file will be created if it does not exist. See AppendData below.
Sep The character that is to separate the elements on each row of the
exported file. Typically, this is vbTab, a space, a comma, semicolor,
or pipe ( | ). Any character may be used.
SelectionOnly If True, only the currently selected cells are
exported. If False, the entire used range of the worksheet is
exported.
AppendData If True and FName exists, data is written to the end of
the text file, preserving the existing contents. If False, the
existing contents of FName are destroyed and only the newly exported
data will appear in the output file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If
If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile