ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Help in VBA code! (https://www.excelbanter.com/excel-discussion-misc-queries/83896-help-vba-code.html)

Martin
Help in VBA code!
 
I have recorded a macro and added some codes for user selecting a file to
import. However when I run there is a double import of text files. Original
VBA after import works well and as follows:

Sub Macro1()


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt", _
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Then I modified the VBA to:

Sub Macro1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt", _
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is wrong with the code?

Ardus Petus
Help in VBA code!
 
If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))

HTH
--
AP

"Martin" a écrit dans le message de
...
I have recorded a macro and added some codes for user selecting a file to
import. However when I run there is a double import of text files.
Original
VBA after import works well and as follows:

Sub Macro1()


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt", _
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Then I modified the VBA to:

Sub Macro1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt", _
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is wrong with the code?

Martin
Help in VBA code!
 
It works thanks! My next step is to loop it so that I can import another
simlar text file at the end of the row after 1st import. How should I code it?

"Ardus Petus" wrote:

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))

HTH
--
AP

"Martin" a écrit dans le message de
...
I have recorded a macro and added some codes for user selecting a file to
import. However when I run there is a double import of text files.
Original
VBA after import works well and as follows:

Sub Macro1()


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt", _
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Then I modified the VBA to:

Sub Macro1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt", _
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is wrong with the code?

Bob Phillips
Help in VBA code!
 

Dim myFileName As Variant

Do

myFileName = Application.GetOpenFilename( _
filefilter:="Text Files, *.Txt", Title:="Pick a File")
If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Else
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))
...
End With
End If

Loop Until myFilename = False

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin" wrote in message
...
It works thanks! My next step is to loop it so that I can import another
simlar text file at the end of the row after 1st import. How should I code
it?

"Ardus Petus" wrote:

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))

HTH
--
AP

"Martin" a écrit dans le message de
...
I have recorded a macro and added some codes for user selecting a file
to
import. However when I run there is a double import of text files.
Original
VBA after import works well and as follows:

Sub Macro1()


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt",
_
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Then I modified the VBA to:

Sub Macro1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt",
_
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is wrong with the code?

Martin
Help in VBA code!
 
Wow its great! It is possible to import them continous in row instead of
column?

"Bob Phillips" wrote:


Dim myFileName As Variant

Do

myFileName = Application.GetOpenFilename( _
filefilter:="Text Files, *.Txt", Title:="Pick a File")
If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Else
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))
...
End With
End If

Loop Until myFilename = False

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin" wrote in message
...
It works thanks! My next step is to loop it so that I can import another
simlar text file at the end of the row after 1st import. How should I code
it?

"Ardus Petus" wrote:

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))

HTH
--
AP

"Martin" a écrit dans le message de
...
I have recorded a macro and added some codes for user selecting a file
to
import. However when I run there is a double import of text files.
Original
VBA after import works well and as follows:

Sub Macro1()


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt",
_
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Then I modified the VBA to:

Sub Macro1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files,
*.Txt", Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '


With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt",
_
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is wrong with the code?

Bob Phillips
Help in VBA code!
 
One way

Dim myFileName As Variant
Dim iLastRow As Long
Do

myFileName = Application.GetOpenFilename( _
filefilter:="Text Files, *.Txt", Title:="Pick a File")
If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Else
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))
.Refresh
Application.ScreenUpdating = False
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & iLastRow).Copy
Range("B1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Columns(1).Delete
Application.ScreenUpdating = True
End With
End If

Loop Until myFileName = False



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin" wrote in message
...
Wow its great! It is possible to import them continous in row instead of
column?

"Bob Phillips" wrote:


Dim myFileName As Variant

Do

myFileName = Application.GetOpenFilename( _
filefilter:="Text Files, *.Txt", Title:="Pick a
File")
If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Else
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))
...
End With
End If

Loop Until myFilename = False

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin" wrote in message
...
It works thanks! My next step is to loop it so that I can import
another
simlar text file at the end of the row after 1st import. How should I
code
it?

"Ardus Petus" wrote:

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" &
myFileName, _
Destination:=Range("A1"))

HTH
--
AP

"Martin" a écrit dans le message
de
...
I have recorded a macro and added some codes for user selecting a
file
to
import. However when I run there is a double import of text files.
Original
VBA after import works well and as follows:

Sub Macro1()


With

ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt",
_
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Then I modified the VBA to:

Sub Macro1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text
Files,
*.Txt", Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '


With

ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\F CI\smartscope.txt",
_
Destination:=Range("A1"))
.Name = "smartscope"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

What is wrong with the code?


All times are GMT +1. The time now is 02:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com