Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CPearsons import text code
Using the code below works well except that it drops the lead zeros
for a couple of the columns. Is their a way to retrieve exactly whats in the text file. Thanks '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' ' ImportTextFile ' This imports a text file into Excel. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Application.ScreenUpdating = False 'On Error GoTo EndMacro: SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #1 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' ' END ImportTextFile '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CPearsons import text code
hi
instead of Cells(RowNdx, ColNdx).Value = TempVal use Cells(RowNdx, ColNdx).text = TempVal might be helpfull ...not sure though -- hemu " wrote: Using the code below works well except that it drops the lead zeros for a couple of the columns. Is their a way to retrieve exactly whats in the text file. Thanks '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' ' ImportTextFile ' This imports a text file into Excel. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Application.ScreenUpdating = False 'On Error GoTo EndMacro: SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #1 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' ' END ImportTextFile '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CPearsons import text code
You could format the range that gets thes values as text.
with Cells(RowNdx, ColNdx) .numberformat = "@" .Value = TempVal end with But I'm not sure that I'd do that for all the fields--if you're importing dates or times or money or ... (where you don't want any leading 0's), it might cause problems down the road. Have you though of leaving the cells formatted as General while you're doing the import, then changing the number format for the fields you want to display leading 0's? After the import... activesheet.range("a1,e1,j1").entirecolumn.numberf ormat = "00000" (to get 3 columns to show 5 digits) " wrote: Using the code below works well except that it drops the lead zeros for a couple of the columns. Is their a way to retrieve exactly whats in the text file. Thanks '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' ' ImportTextFile ' This imports a text file into Excel. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Application.ScreenUpdating = False 'On Error GoTo EndMacro: SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #1 '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' ' END ImportTextFile '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''' End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CPearsons import text code
Try using .formula instead of .value
Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
VBA code to import stacked text files | Excel Programming | |||
How to Use Chip Pearson's Text Import Code | Excel Programming | |||
Import HTML code from a text file. | Excel Programming | |||
Use VBA code to open a text file with the import wizard | Excel Programming |