Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Help in VBA code!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Martin
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default 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?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Martin
 
Posts: n/a
Default 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?



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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?





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Martin
 
Posts: n/a
Default 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?






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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?








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
code not unique find latest date Barbara Wiseman Excel Discussion (Misc queries) 3 December 11th 05 08:50 AM
VLOOKUP for Zip Code Ranges JerseyJR Excel Worksheet Functions 2 September 6th 05 06:37 PM
Conform a total to a list of results? xmaveric Excel Discussion (Misc queries) 1 August 21st 05 07:22 PM
Macro for changing text to Proper Case JPriest Excel Worksheet Functions 3 August 8th 05 09:31 PM
close form code tkaplan Excel Discussion (Misc queries) 1 June 3rd 05 10:49 PM


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement