Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to export unlocked cells to a row in a text file.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to export a range of cells on a worksheet to a text 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) | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming |