Import Data

[VegasNath]

I am trying to create a macro that imports and formats a text file into excel. So far, I have the following:

Code: Select all

Sub ImportBC450()

Dim Msg As String
Dim wb As Workbook
Dim ws As Worksheet

Dim varFile As Variant

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

    Msg = vbCrLf & "You are about to import a source data file."
    Msg = Msg & vbCrLf & vbCrLf & "You will be faced with a 'file open' dialogue, where you will need to select a source BC450 to import."
    Msg = Msg & vbCrLf & vbCrLf & "Are you sure that you want to continue?"
    Msg = Msg & vbCrLf & vbCrLf
    
    If MsgBox(Msg, vbQuestion + vbYesNo, "Source Data File") = vbNo Then
        Exit Sub
    Else
            
        varFile = Application.GetOpenFilename("All files (*.*), *.*")
        
        If VarType(varFile) = vbBoolean Then
            'User canceled dialog
                Beep
            Exit Sub
        End If
        
        'Import selected file
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & varFile, Destination:=Range("A1"))
            .Name = ""
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .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)
            .TextFileFixedColumnWidths = Array(19, 14, 8, 22, 16, 45, 21, 16)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    
    End If

End Sub

I have an issue where running the macro in 2007, only the first 65536 rows are imported and I cannot see why?

Also, if possible, I would like to specify a row to start the import from, eg: row 74333. Is this possible?

Thanks for any advice.

[Jan Karel Pieterse]

A question first: Why add a querytable each time? After the first run, simply refreshing the QT should bring up the file open dialog anyway.

The workbook you are importing into is likely in xls format, which means Excel limits your import to 65536 rows because it is in compatibility mode (shown on Excel's title bar).

[VegasNath]

Hi Jan Karel, thankyou for your response.

The answer to your questiion is that I am setting up a template that will be a read only file to the user. A query will be added each time and then the workbook will be saved as an output file. No refresh will be required. If I am going about this in the wrong way, please let me know.

I think that this is probably better suited to 2007, so I have saved the file as a 2007 workbook.

I have amended the line:

Code: Select all

.TextFileStartRow = 74334

.... which suits my needs.

Now for the data manipulation.

Code: Select all

Rows("96:16726, 16793:319951, 320047:322305, 322372:50000").Delete Shift:=xlUp

Why does this not work?

Thanks

[HansV]

Try using Range instead of Rows. Rows doesn't work with multiple areas, Range does.

Oh, and don't you mean 500000 instead of 50000?

[VegasNath]

Range does not work for me either.

[Jan Karel Pieterse]

Best to do them in turn then, working bottom-up:

Range("322372:50000").EntireRow.Delete
Range("320047:322305").EntireRow.Delete
Range("16793:319951").EntireRow.Delete
Range("96:16726").EntireRow.Delete

[HansV]

Are you sure you're working with an Excel 2007 sheet (1,048,576 rows), and not with an Excel 97-2003 sheet (65,536 rows)? The line

Range("96:16726, 16793:319951, 320047:322305, 322372:500000").Delete Shift:=xlUp

works OK when I try it.

[VegasNath]

.....

Range("96:16726, 16793:319951, 320047:322305, 322372:500000").Delete Shift:=xlUp

works OK when I try it.

I think my syntax was wrong when I tried it.

It works for me now also, Thanks!