I'm importing from a generic *.dat file. I can get the import done fine, I
then have to edit the data in a way that lets me analyse it. When I use the
text to columns function it asks if I want to replace the contects of the
destination cells. I will always want to, but I don't see a way of defaulting
the overwrite so it doesn't ask me, I just want it to do it every time. I
have a separate import of an Access file that works fine with no prompts.

Here's my *.dat code so far:
--------------------------------------------------------------------------------------------
Sheets("Region1Mail").Select
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;\\Fi leServer\Labels\Inkjet\TKWE101008STD.dat", Destination:=Range("A1"))
.Name = "TKWE101008STD"
.FieldNames = False '
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False '
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False '
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(10, 2), Array(43, 2), Array(70,
2), Array(105, 2), _
Array(135, 2), Array(140, 9)), TrailingMinusNumbers:=True

'Need some way to overwrite destination cells by default
--------------------------------------------------------------------------------------------


Here's my Access import code which works fine:

Sheets("Region1Mail").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=\\Prism1\C\Labels\" & Region1MailFileHeader &
MonthDayYear & "STD.mdb;Mode=Share Deny W" _
, _
"rite;Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Eng" _
, _
"ine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:" _
, _
"New Database Password="""";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on " _
, _
"Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=ActiveCell)
.CommandType = xlCmdTable
.CommandText = Array("DATA")
.Name = "Region1Mail"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "\\Prism1\C\Labels\" & Region1MailFileHeader &
MonthDayYear & "STD.mdb"
.Refresh BackgroundQuery:=False
End With
--------------------------------------------------------------------------------------------
Thanks in advance, don

Using HELP, look up "DisplayAlerts".

You can turn off your alerts with VBA code:

Application.DisplayAlerts = False

Remember to turn them back on:

Application.DisplayAlerts = True

"Don M." wrote:

I'm importing from a generic *.dat file. I can get the import done fine, I
then have to edit the data in a way that lets me analyse it. When I use the
text to columns function it asks if I want to replace the contects of the
destination cells. I will always want to, but I don't see a way of defaulting
the overwrite so it doesn't ask me, I just want it to do it every time. I
have a separate import of an Access file that works fine with no prompts.

Here's my *.dat code so far:
--------------------------------------------------------------------------------------------
Sheets("Region1Mail").Select
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;\\Fi leServer\Labels\Inkjet\TKWE101008STD.dat", Destination:=Range("A1"))
.Name = "TKWE101008STD"
.FieldNames = False '
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False '
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False '
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(10, 2), Array(43, 2), Array(70,
2), Array(105, 2), _
Array(135, 2), Array(140, 9)), TrailingMinusNumbers:=True

'Need some way to overwrite destination cells by default
--------------------------------------------------------------------------------------------


Here's my Access import code which works fine:

Sheets("Region1Mail").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=\\Prism1\C\Labels\" & Region1MailFileHeader &
MonthDayYear & "STD.mdb;Mode=Share Deny W" _
, _
"rite;Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Eng" _
, _
"ine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:" _
, _
"New Database Password="""";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on " _
, _
"Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
), Destination:=ActiveCell)
.CommandType = xlCmdTable
.CommandText = Array("DATA")
.Name = "Region1Mail"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "\\Prism1\C\Labels\" & Region1MailFileHeader &
MonthDayYear & "STD.mdb"
.Refresh BackgroundQuery:=False
End With
--------------------------------------------------------------------------------------------
Thanks in advance, don