Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2373
  • Last Modified:

Export from textpad to excel.

Does anyone has a shorter version of the code below..All I am trying to do is input .txt file to excel..My only concern is one line pointed below..I want the flexibility to change the numbers on this line.

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Program Files\" & myprojectName & "\This.TAB", Destination:=Range("A1"))
        .Name = "This"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(7, 7, 8, 15, 4, 12, 4, 16, 3, 10, 8, 3, 10, 10, 10, 10) <-----------------
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Thank you.
0
Aiysha
Asked:
Aiysha
  • 3
  • 2
2 Solutions
 
[ fanpages ]IT Services ConsultantCommented:
How about...

   Workbooks.OpenText Filename:=C:\Program Files\" & myprojectName & "\This.TAB", Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, 1), Array(7, 1), Array(14, 1), Array(22, 1), Array(37, 1))

etc..?
0
 
inthedarkCommented:
To handle large volumes of data into excel I found quickest method is to use Paste.

So you load your data form the file:

OK = ExcelCreateOK(ReadFIle("C:\MyData\Text.txt"))

You file must be formated like this:

Field1 + vbTab + Field2 + vbTab + LastColumnField + vbCRLF

Before you paste your data you can set the columns widths with a little mod to the code. I will post later.

Public Function ExcelCreateOK(FromData As String, Optional psFileName As String = "") As Boolean

' Fires up Excel and pastes data into a new workbook
' See class zExcel for a better version of this which allows formatting
' of columns

' OK = GF.ExcelCreateOK(sDataToPaste)

Const zxlNormal = -4143

Dim IDE As Boolean

' Are we running in IDE or EXE mode?
On Error Resume Next
Err.Clear
Debug.Print 1 / 0
If Err.Number <> 0 Then
    IDE = True
End If
If IDE Then
    On Error GoTo 0
Else
    On Error GoTo ErrorTrap
End If

' Create the Excel objects
Dim ExcelApp ' As Excel.Application
On Error Resume Next

Set ExcelApp = CreateObject("Excel.Application")


Dim WB ' As Excel.Workbook

' If no filename make visible to hold on screen
ExcelApp.Visible = Len(psFileName) = 0

' create a blank sheet
Set WB = ExcelApp.Workbooks.Add

' set app focus to the new sheet
WB.Activate
ExcelApp.Range("A1").Select

' stuff the data onto clipboard
Clipboard.Clear
Clipboard.SetText FromData

' move clipboard to Excel
ExcelApp.ActiveSheet.Paste
DoEvents
Clipboard.Clear ' release memory
DoEvents
If Len(psFileName) > 0 Then
   
    ' if a file name save and close Excel
    ExcelApp.ActiveWorkbook.SaveAs FileName:=psFileName, FileFormat:=zxlNormal, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    ExcelApp.ActiveWorkbook.Close
    ExcelApp.Quit
    Set WB = Nothing
    Set ExcelApp = Nothing
Else
    ' Allow Excel to stay on screen
    Set WB = Nothing
    Set ExcelApp = Nothing
End If

ExcelCreateOK = True
Exit Function

ErrorTrap:
ErrN = Err.Number
ErrD = Err.Description
On Error Resume Next

ExcelCreateOK = False

End Function


0
 
inthedarkCommented:
A little bit more code, but just a once in a lilfe time addition to your code and then you can forget about exporting to excel FOR EVER.  I just need an SQL statement. Or a disconnected recorset loaded with data. And 2 seconds later I have the report in Excel.

I created a class which could generate an Excel report from a recordset. With all of the trinckets of a nice report, titles, sub totals, bold bits, etc.

The cute move was to disconnect the recordset to allow for dynamic processing of column data, say for calculated columns, etc. I create a little form which allows editing of tiles, field captions and formats, sub totalling details, etc. And that was it, one day later no more coding ever.

So I create an AddColumn sub which needed just caption, width, justification and format spec, totaling details, for each column. Column data was saved into a UTD array (mvColdata)

Here is how the export starts....

To set column widths before past of data.

' define font
EA.Cells.Select
With EA.Selection.Font
    .Name = "Arial"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = zxlUnderlineStyleNone
    .ColorIndex = zxlAutomatic
End With
EA.Selection.Font.Bold = False

Dim lc As Long


lc = 0
Do While lc <= mlColumns
   
    EA.Columns(mvColdata(lc).Name + ":" + mvColdata(lc).Name).Select
    With EA.Selection
        .VerticalAlignment = &HFFFFEFF5 'Excel.Constants.xlBottom
        .WrapText = False
        .Orientation = 0
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
        If Len(mvColdata(lc).FormatSpec) > 0 Then
            On Error Resume Next
            .NumberFormat = mvColdata(lc).FormatSpec
            On Error GoTo 0
        Else
            .NumberFormat = "General"
        End If
        If mvColdata(lc).Characters > 0 Then
            .ColumnWidth = mvColdata(lc).Characters
        End If
       
        Select Case mvColdata(lc).align
            Case Is = zExAlignment.zexLeft
                '.HorizontalAlignment = Excel.Constants.xlLeft
                .HorizontalAlignment = &HFFFFEFDD  'left
            Case Is = zExAlignment.zexRight
                 .HorizontalAlignment = &HFFFFEFC8  'right
            Case Else
                .HorizontalAlignment = &HFFFFEFF4   'center
        End Select
       
    End With
   
    lc = lc + 1
Loop

EA.Range("A1").Select


If Len(DataToPaste) > 0 Then
    Clipboard.Clear
    Clipboard.SetText DataToPaste
    EA.ActiveSheet.Paste 'paste into excel
    Clipboard.Clear
End If

My version of this class then goes on to setup Bold areas, underlines etc.

Just an idea for you. Do the job once, take a break forever.
0
 
[ fanpages ]IT Services ConsultantCommented:
That's fine with me, as long as it is OK with inthedark, thank you, Dan.
0
 
inthedarkCommented:
ok
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now