Numbers 0000 will = 0 when converted to a csv file via macro. i want my 0000
to be the value in the csv file not just 0 and 0999 to be a value not 999.
this is very important to port phone numbers in to database. the spreadsheet
has a format of text and a data validation of length 4 characters as a min
and a max. i have a 41 sheet spreadsheet with multiple columns. here is the
current macro: Please let me know what i can do, thanks.



Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer

'**** Allows you to choose your delimenter - advised not to use comma
Sep = InputBox("Enter a single delimiter character (e.g., pipe or
semi-colon)", _
"Export To Text File")


For Each wsSheet In Worksheets
wsSheet.Activate

'**** make all cells in upper case
' Sub makeupper()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then c.Value = UCase(c)
Next
' End Sub
'*****

nFileNum = FreeFile
Open "C:\00_Mintz\CSG_Proj\PROJECTS\baselines\Carol_Qui nn\CSV\" &
wsSheet.Name & ".csv" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
'_
' MsgBox("Do You Want To Export The Entire Worksheet?", _
' vbYesNo, "Export To Text File") = vbNo
Close nFileNum
Next wsSheet

End Sub

Public Sub ExportToTextFile(nFileNum As Integer, _
Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
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

'--------------------------------------
' The following if statement allows you to choose
' selection or the whole sheet to be converted
Application.ScreenUpdating = False
On Error GoTo EndMacro:

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
'--------------------------------------

For RowNdx = StartRow + 1 To EndRow 'skips the header
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = "" 'configures values around the comma values
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #nFileNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub





thanks in advance
cg

CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

should put in your numbers as they appear: 0999

If you look at the file in Notepad you say 0000 woulld appear there as 0 - I
think not. I think perhaps you are opening the file in Excel and that is
probably where they are being converted to zero. Anyway, writing the file
should be as you want.

Just an added, you could change the above line to

CellValue = cells(RowNdx, ColNdx).Text

but the result should be the same as you have now.

--
Regards,
Tom Oglvy




"Mintz87" wrote in message
...
Numbers 0000 will = 0 when converted to a csv file via macro. i want my
0000
to be the value in the csv file not just 0 and 0999 to be a value not 999.
this is very important to port phone numbers in to database. the
spreadsheet
has a format of text and a data validation of length 4 characters as a min
and a max. i have a 41 sheet spreadsheet with multiple columns. here is
the
current macro: Please let me know what i can do, thanks.



Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer

'**** Allows you to choose your delimenter - advised not to use comma
Sep = InputBox("Enter a single delimiter character (e.g., pipe or
semi-colon)", _
"Export To Text File")


For Each wsSheet In Worksheets
wsSheet.Activate

'**** make all cells in upper case
' Sub makeupper()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then c.Value = UCase(c)
Next
' End Sub
'*****

nFileNum = FreeFile
Open "C:\00_Mintz\CSG_Proj\PROJECTS\baselines\Carol_Qui nn\CSV\" &
wsSheet.Name & ".csv" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
'_
' MsgBox("Do You Want To Export The Entire Worksheet?", _
' vbYesNo, "Export To Text File") = vbNo
Close nFileNum
Next wsSheet

End Sub

Public Sub ExportToTextFile(nFileNum As Integer, _
Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
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

'--------------------------------------
' The following if statement allows you to choose
' selection or the whole sheet to be converted
Application.ScreenUpdating = False
On Error GoTo EndMacro:

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
'--------------------------------------

For RowNdx = StartRow + 1 To EndRow 'skips the header
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = "" 'configures values around the comma
values
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #nFileNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub





thanks in advance
cg

i look at the csv files in textpad, emeditor, notepad, textpad, and unix vi -
they all show that 0000 is converted to 0 and the 0999 is converted to 999.
i tired this and kept getting an error "run time error", "application defined
or object defined error"

thanks

"Tom Ogilvy" wrote:

CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

should put in your numbers as they appear: 0999

If you look at the file in Notepad you say 0000 woulld appear there as 0 - I
think not. I think perhaps you are opening the file in Excel and that is
probably where they are being converted to zero. Anyway, writing the file
should be as you want.

Just an added, you could change the above line to

CellValue = cells(RowNdx, ColNdx).Text

but the result should be the same as you have now.

--
Regards,
Tom Oglvy




"Mintz87" wrote in message
...
Numbers 0000 will = 0 when converted to a csv file via macro. i want my
0000
to be the value in the csv file not just 0 and 0999 to be a value not 999.
this is very important to port phone numbers in to database. the
spreadsheet
has a format of text and a data validation of length 4 characters as a min
and a max. i have a 41 sheet spreadsheet with multiple columns. here is
the
current macro: Please let me know what i can do, thanks.



Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer

'**** Allows you to choose your delimenter - advised not to use comma
Sep = InputBox("Enter a single delimiter character (e.g., pipe or
semi-colon)", _
"Export To Text File")


For Each wsSheet In Worksheets
wsSheet.Activate

'**** make all cells in upper case
' Sub makeupper()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then c.Value = UCase(c)
Next
' End Sub
'*****

nFileNum = FreeFile
Open "C:\00_Mintz\CSG_Proj\PROJECTS\baselines\Carol_Qui nn\CSV\" &
wsSheet.Name & ".csv" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
'_
' MsgBox("Do You Want To Export The Entire Worksheet?", _
' vbYesNo, "Export To Text File") = vbNo
Close nFileNum
Next wsSheet

End Sub

Public Sub ExportToTextFile(nFileNum As Integer, _
Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
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

'--------------------------------------
' The following if statement allows you to choose
' selection or the whole sheet to be converted
Application.ScreenUpdating = False
On Error GoTo EndMacro:

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
'--------------------------------------

For RowNdx = StartRow + 1 To EndRow 'skips the header
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = "" 'configures values around the comma
values
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #nFileNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub





thanks in advance
cg

If you are talking about the change I suggested, you must have screwed it
up. You already have

CellValue = -- useless junk( Cells(RowNdx, ColNdx).Value, Useless Junk--

I just suggested this change:
CellValue = cells(RowNdx, ColNdx).Text

This change would not cause an error.

The only thing that might do what you describe is if you have textvalues in
your cells and but the cells are actually formatted as general or with
something like "#". In either case, my suggested change would fix that.

Right he
Print #nFileNum, WholeLine

your code writes the whole line as a string. So no conversion would take
place at that point. The original code would normally sustain the
formatting with the rare exception I described. My correction would sustain
formatting regardless. the value would be written as it appears on the
sheet.

--
Regards,
Tom Ogilvy

"Mintz87" wrote in message
...
i look at the csv files in textpad, emeditor, notepad, textpad, and unix
vi -
they all show that 0000 is converted to 0 and the 0999 is converted to
999.
i tired this and kept getting an error "run time error", "application
defined
or object defined error"

thanks

"Tom Ogilvy" wrote:

CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

should put in your numbers as they appear: 0999

If you look at the file in Notepad you say 0000 woulld appear there as
0 - I
think not. I think perhaps you are opening the file in Excel and that
is
probably where they are being converted to zero. Anyway, writing the
file
should be as you want.

Just an added, you could change the above line to

CellValue = cells(RowNdx, ColNdx).Text

but the result should be the same as you have now.

--
Regards,
Tom Oglvy




"Mintz87" wrote in message
...
Numbers 0000 will = 0 when converted to a csv file via macro. i want
my
0000
to be the value in the csv file not just 0 and 0999 to be a value not
999.
this is very important to port phone numbers in to database. the
spreadsheet
has a format of text and a data validation of length 4 characters as a
min
and a max. i have a 41 sheet spreadsheet with multiple columns. here
is
the
current macro: Please let me know what i can do, thanks.



Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer

'**** Allows you to choose your delimenter - advised not to use comma
Sep = InputBox("Enter a single delimiter character (e.g., pipe or
semi-colon)", _
"Export To Text File")


For Each wsSheet In Worksheets
wsSheet.Activate

'**** make all cells in upper case
' Sub makeupper()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then c.Value = UCase(c)
Next
' End Sub
'*****

nFileNum = FreeFile
Open "C:\00_Mintz\CSG_Proj\PROJECTS\baselines\Carol_Qui nn\CSV\" &
wsSheet.Name & ".csv" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
'_
' MsgBox("Do You Want To Export The Entire Worksheet?", _
' vbYesNo, "Export To Text File") = vbNo
Close nFileNum
Next wsSheet

End Sub

Public Sub ExportToTextFile(nFileNum As Integer, _
Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
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

'--------------------------------------
' The following if statement allows you to choose
' selection or the whole sheet to be converted
Application.ScreenUpdating = False
On Error GoTo EndMacro:

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
'--------------------------------------

For RowNdx = StartRow + 1 To EndRow 'skips the header
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = "" 'configures values around the
comma
values
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #nFileNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub





thanks in advance
cg

Tom -

thanks for your help - i did screw it up the first time around.

have a nice day.

"Tom Ogilvy" wrote:

If you are talking about the change I suggested, you must have screwed it
up. You already have

CellValue = -- useless junk( Cells(RowNdx, ColNdx).Value, Useless Junk--

I just suggested this change:
CellValue = cells(RowNdx, ColNdx).Text

This change would not cause an error.

The only thing that might do what you describe is if you have textvalues in
your cells and but the cells are actually formatted as general or with
something like "#". In either case, my suggested change would fix that.

Right he
Print #nFileNum, WholeLine

your code writes the whole line as a string. So no conversion would take
place at that point. The original code would normally sustain the
formatting with the rare exception I described. My correction would sustain
formatting regardless. the value would be written as it appears on the
sheet.

--
Regards,
Tom Ogilvy

"Mintz87" wrote in message
...
i look at the csv files in textpad, emeditor, notepad, textpad, and unix
vi -
they all show that 0000 is converted to 0 and the 0999 is converted to
999.
i tired this and kept getting an error "run time error", "application
defined
or object defined error"

thanks

"Tom Ogilvy" wrote:

CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

should put in your numbers as they appear: 0999

If you look at the file in Notepad you say 0000 woulld appear there as
0 - I
think not. I think perhaps you are opening the file in Excel and that
is
probably where they are being converted to zero. Anyway, writing the
file
should be as you want.

Just an added, you could change the above line to

CellValue = cells(RowNdx, ColNdx).Text

but the result should be the same as you have now.

--
Regards,
Tom Oglvy




"Mintz87" wrote in message
...
Numbers 0000 will = 0 when converted to a csv file via macro. i want
my
0000
to be the value in the csv file not just 0 and 0999 to be a value not
999.
this is very important to port phone numbers in to database. the
spreadsheet
has a format of text and a data validation of length 4 characters as a
min
and a max. i have a 41 sheet spreadsheet with multiple columns. here
is
the
current macro: Please let me know what i can do, thanks.



Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer

'**** Allows you to choose your delimenter - advised not to use comma
Sep = InputBox("Enter a single delimiter character (e.g., pipe or
semi-colon)", _
"Export To Text File")


For Each wsSheet In Worksheets
wsSheet.Activate

'**** make all cells in upper case
' Sub makeupper()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then c.Value = UCase(c)
Next
' End Sub
'*****

nFileNum = FreeFile
Open "C:\00_Mintz\CSG_Proj\PROJECTS\baselines\Carol_Qui nn\CSV\" &
wsSheet.Name & ".csv" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
'_
' MsgBox("Do You Want To Export The Entire Worksheet?", _
' vbYesNo, "Export To Text File") = vbNo
Close nFileNum
Next wsSheet

End Sub

Public Sub ExportToTextFile(nFileNum As Integer, _
Sep As String, SelectionOnly As Boolean)

Dim WholeLine As String
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

'--------------------------------------
' The following if statement allows you to choose
' selection or the whole sheet to be converted
Application.ScreenUpdating = False
On Error GoTo EndMacro:

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
'--------------------------------------

For RowNdx = StartRow + 1 To EndRow 'skips the header
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = "" 'configures values around the
comma
values
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #nFileNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

End Sub





thanks in advance
cg