fileopen problem

D

DeCaelo

New Member
Joined
Mar 17, 2011
Messages
29
I keep failing on trying to add a fileopen command in VBA :(

I just want to bring up a dialog box for the user to select a .txt file, which gets arranged as per the instructions in the code.

The below is the code I'm using keeps bringing up a "Compile Error: For Without Next".

Sub InsertData()

ans = MsgBox _
("Please select the report you wish to import.", _
vbOKOnly, "Blah, blah Report")

Dim txtfile As Variant
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add "Text files", "*.txt", 1

If .Show = -1 Then
For Each txtfile In .SelectedItems

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & txtfile, Destination:=Sheets("Sheet 1"). _
Range("b65536").End(xlUp).Offset(1, 0))
.Name = filetoimport
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 2, 2, 1, 1, 2, 1, 2, 2, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Booking"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Account Type"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Owner"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Invoice"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Owner 2"
Range("H1").Select
ActiveCell.FormulaR1C1 = "REF 1"
Range("I1").Select
ActiveCell.FormulaR1C1 = "REF 2"
Range("J1").Select
ActiveCell.FormulaR1C1 = "SVC"
Range("K1").Select
ActiveCell.FormulaR1C1 = "SVC type"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Branch"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Branch Name"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Col postcode"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Del postcode"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Legs"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Miles"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Booked By"
Range("S1").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("S1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Time Booked"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Requested Pickup"
Range("U1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("V1").Select
ActiveCell.FormulaR1C1 = "Time 2"
Range("W1").Select
ActiveCell.FormulaR1C1 = "Requested Delivery"
Range("X1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("Y1").Select
ActiveCell.FormulaR1C1 = "Time 2"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Last Notes"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Col Arrival"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "POC time"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Confirmed Delivery Time"
Range("AD1").Select
ActiveWindow.SmallScroll Down:=-12
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Confirmed Time"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "ETA revision 1"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "Revised Time 1"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "ETA revision 2"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "Revised time 2"
Range("AL1").Select
ActiveCell.FormulaR1C1 = "ETA revision 3"
Range("AM1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "Revised Time 2"
Range("AO1").Select
ActiveCell.FormulaR1C1 = "ETA revision 4"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "Revised Time 3"
Range("AP1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AQ1").Select
ActiveCell.FormulaR1C1 = "Revised Time 4"
Range("AR1").Select
ActiveCell.FormulaR1C1 = "Del Arrived"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "POD date"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "POD Name"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "Revised Reason"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "POD entered by"
Range("AX1").Select
ActiveCell.FormulaR1C1 = "POD entered Time"
Range("AY1").Select
ActiveWindow.SmallScroll Down:=-6
Range("BA1").Select
ActiveCell.FormulaR1C1 = "Revisions"
Range("BB1").Select
ActiveCell.FormulaR1C1 = "Request Number"
Cells.Select
Range("AL1").Activate
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:G").Select
Selection.EntireColumn.Hidden = True
Columns("K:M").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("S:S").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Columns("W:W").Select
Selection.Cut
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Range("X8").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("W:X").Select
Selection.Cut
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Columns("Z:AB").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
Columns("Y:AC").Select
Columns("Y:AC").EntireColumn.AutoFit
Columns("W:W").Select
Selection.EntireColumn.Hidden = True
Columns("Z:Z").Select
Selection.Insert Shift:=xlToRight
Range("Z1").Select
ActiveCell.FormulaR1C1 = "SCORECARD"
Range("Z2").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("S58").Select
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[56]C[-5]=""BT"",IF(AND(RC[-2]>=RC[-6],RC[-2]<=R[56]C[-12]),""ok"",""BT Fail""),IF(RC[-5]=""AF"",IF(AND(RC[-2]>RC[-6]),""ok"",""AF Fail""),IF(RC[-5]=""BY"",IF(AND(RC[-2]<RC[-6]),""ok"",""BY Fail""))))"
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z48")
Range("Z2:Z48").Select
Range("Z12").Select
ActiveWindow.SmallScroll Down:=36
Range("Z48").Select
ActiveWindow.SmallScroll Down:=-54
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
Columns("AV:AV").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("BC17").Select
Columns("AZ:AZ").ColumnWidth = 8.71
Columns("AZ:BD").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B2").Select
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
End With
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Fails"
Range("C2").Select
With ActiveWindow
.SplitColumn = 2
.SplitRow = 1
End With
End Sub
 
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
There is something wrong with the formula. But that could be vastly condensed

Code: 
Option Explicit

Sub InsertData()

ans = MsgBox _
("Please select the report you wish to import.", _
vbOKOnly, "Blah, blah Report")

Dim txtfile As Variant
With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Add "Text files", "*.txt", 1
    
    If .Show = -1 Then
        For Each txtfile In .SelectedItems
            
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & txtfile, Destination:=Sheets("Sheet 1"). _
        Range("b65536").End(xlUp).Offset(1, 0))
        .Name = filetoimport
        .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 = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 2, 2, 1, 1, 2, 1, 2, 2, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Rows(1).Insert
    Range("A1").Resize(, 18).Value = Array("Booking", "Account", "Account Name", "Account Type", "Owner", "Invoice", "Owner 2", "REF 1", "REF 2", "SVC", "SVC type", "Branch", "Branch Name", "Col postcode", "Del postcode", "Legs", "Miles", "Booked By")
    Range("S1").Resize(, 33).Value = Array("Time Booked", "Requested Pickup", "Type", "Time 2", "Requested Delivery", "Type", "Time 2", "Last Notes", "Col Arrival", "POC time", "Confirmed Delivery Time", "Confirmed Time", "ETA revision 1", "Reason", "Revised Time 1", "ETA revision 2", "Reason", "Revised time 2", "ETA revision 3", "Reason", "Revised Time 2", "ETA revision 4", "Revised Time 3", "Reason", "Revised Time 4", "Del Arrived", "POD date", "POD Name", "Revised Reason", "Status", "POD entered by", "POD entered Time", "Revisions")
    Range("BB1").Value = "Request Number"
    Range("AL1").AutoFilter
    Cells.EntireColumn.AutoFit
    Columns("C:G").Hidden = True
    Columns("K:M").Hidden = True
    Columns("P").Hidden = True
    Columns("R:S").Hidden = True
    Columns("W").Cut
    Columns("AC").Insert
    Range("X8").Select
    Columns("W:X").Cut
    Columns("AC").Insert
    Columns("Z:AB").Hidden = True
    Columns("Y:AC").AutoFit
    Columns("W").Hidden = True
    Columns("Z").Insert
    Range("Z1").Value = "SCORECARD"
    Range("Z2").Select
    Range("Z2").FormulaR1C1 = _
"=IF(R[56]C[-5]=""BT"",IF(AND(RC[-2]>=RC[-6],RC[-2]<=R[56]C[-12]),""ok"",""BT Fail""),IF(RC[-5]=""AF"",IF(AND(RC[-2]>RC[-6]),""ok"",""AF Fail""),IF(RC[-5]=""BY"",IF(AND(RC[-2] Range("Z2")
Columns("AZ").ColumnWidth = 8.71
Columns("AZ:BD").Column.Hidden = True
Range("B2").Insert
Range("B1").Value = "Fails"
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro for
Replies
2
Views
117
Sanderb
S
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
1K
cstuder
C
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
663
ghrek
G
R
  • Question Question
Formatting using VBA
Replies
0
Views
553
RandyD123
R

Forum statistics

Threads
1,224,531
Messages
6,179,379
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top