takformaten
Board Regular
- Joined
- Jan 19, 2004
- Messages
- 75
Folks, I am going NUTS!!!
OK, what I am trying to do is make a command button. (Easy), that when you press ist imports data from a .txt file, formats it [delimiters etc.] (Easy) and then pastes it into a newly created worksheet. (arrrgh)
Code is: Private Sub CommandButton2_Click()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Data\WS#CPD03_01.TXT" _
, Destination:=Range("A1"))
.Name = "WS#CPD03_01"
.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, 2, 1, 1, 2, 2, 2, 1, 2, 1)
.TextFileFixedColumnWidths = Array(19, 70, 3, 37, 26, 9, 11, 20, 26, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
On clicking the button my Excel tells me the "destination range was not on the same worksheet that the query table was being created on" (Run time error) When I click on help I only see a grey box.
Is it me? Is it Excel? Is there help?
OK, what I am trying to do is make a command button. (Easy), that when you press ist imports data from a .txt file, formats it [delimiters etc.] (Easy) and then pastes it into a newly created worksheet. (arrrgh)
Code is: Private Sub CommandButton2_Click()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Data\WS#CPD03_01.TXT" _
, Destination:=Range("A1"))
.Name = "WS#CPD03_01"
.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, 2, 1, 1, 2, 2, 2, 1, 2, 1)
.TextFileFixedColumnWidths = Array(19, 70, 3, 37, 26, 9, 11, 20, 26, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
On clicking the button my Excel tells me the "destination range was not on the same worksheet that the query table was being created on" (Run time error) When I click on help I only see a grey box.
Is it me? Is it Excel? Is there help?