By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,057 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,057 IT Pros & Developers. It's quick & easy.

TextFile Automation

P: 9
Hello,
I have written a macro that will import 4 different text files into 1 excel worksheet.
After each import i append an ID(5th column) to distingush the source of the data.
My problem is how to make sure that it always imports starting from the next free cell & to make sure it appends the ID correctly.
Here is what i have for now:
Expand|Select|Wrap|Line Numbers
  1.  
  2.     Range("A1").Select
  3.     With ActiveSheet.QueryTables.Add(Connection:= _
  4.         "TEXT;C:\WK44.Net\LAG\MFS_list.csv" _
  5.         , Destination:=Range("A1"))
  6.         .Name = "MFS_list_1"
  7.         .FieldNames = True
  8.         .RowNumbers = False
  9.         .FillAdjacentFormulas = False
  10.         .PreserveFormatting = True
  11.         .RefreshOnFileOpen = False
  12.         .RefreshStyle = xlInsertDeleteCells
  13.         .SavePassword = False
  14.         .SaveData = True
  15.         .AdjustColumnWidth = True
  16.         .RefreshPeriod = 0
  17.         .TextFilePromptOnRefresh = False
  18.         .TextFilePlatform = 437
  19.         .TextFileStartRow = 1
  20.         .TextFileParseType = xlDelimited
  21.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  22.         .TextFileConsecutiveDelimiter = False
  23.         .TextFileTabDelimiter = True
  24.         .TextFileSemicolonDelimiter = True
  25.         .TextFileCommaDelimiter = False
  26.         .TextFileSpaceDelimiter = False
  27.         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
  28.         .TextFileTrailingMinusNumbers = True
  29.         .Refresh BackgroundQuery:=False
  30.     End With
  31.     Range("A2").Select
  32.     Selection.End(xlToRight).Select
  33.     Range("E2").Select
  34.     ActiveCell.FormulaR1C1 = "1"
  35.     Range("E2").Select
  36.     Selection.Copy
  37.     Range("E3:E6").Select
  38.     ActiveSheet.Paste
  39.     Range("A7").Select
  40.     Application.CutCopyMode = False
  41.     With ActiveSheet.QueryTables.Add(Connection:= _
  42.         "TEXT;C:\WK44.Net\IBD\MFS_list.csv" _
  43.         , Destination:=Range("A7"))
  44.         .Name = "MFS_list_2"
  45.         .FieldNames = True
  46.         .RowNumbers = False
  47.         .FillAdjacentFormulas = False
  48.         .PreserveFormatting = True
  49.         .RefreshOnFileOpen = False
  50.         .RefreshStyle = xlInsertDeleteCells
  51.         .SavePassword = False
  52.         .SaveData = True
  53.         .AdjustColumnWidth = True
  54.         .RefreshPeriod = 0
  55.         .TextFilePromptOnRefresh = False
  56.         .TextFilePlatform = 437
  57.         .TextFileStartRow = 1
  58.         .TextFileParseType = xlDelimited
  59.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  60.         .TextFileConsecutiveDelimiter = False
  61.         .TextFileTabDelimiter = True
  62.         .TextFileSemicolonDelimiter = True
  63.         .TextFileCommaDelimiter = False
  64.         .TextFileSpaceDelimiter = False
  65.         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
  66.         .TextFileTrailingMinusNumbers = True
  67.         .Refresh BackgroundQuery:=False
  68.     End With
  69.     Selection.End(xlToRight).Select
  70.     Range("E8").Select
  71.     ActiveCell.FormulaR1C1 = "2"
  72.     Range("E9").Select
  73.     ActiveCell.FormulaR1C1 = "2"
  74.     Range("A1").Select
  75.     Selection.End(xlDown).Select
  76.     Range("A10").Select
  77.     With ActiveSheet.QueryTables.Add(Connection:= _
  78.         "TEXT;C:\WK44.Net\BEN\MFS_list.csv" _
  79.         , Destination:=Range("A10"))
  80.         .Name = "MFS_list_3"
  81.         .FieldNames = True
  82.         .RowNumbers = False
  83.         .FillAdjacentFormulas = False
  84.         .PreserveFormatting = True
  85.         .RefreshOnFileOpen = False
  86.         .RefreshStyle = xlInsertDeleteCells
  87.         .SavePassword = False
  88.         .SaveData = True
  89.         .AdjustColumnWidth = True
  90.         .RefreshPeriod = 0
  91.         .TextFilePromptOnRefresh = False
  92.         .TextFilePlatform = 437
  93.         .TextFileStartRow = 1
  94.         .TextFileParseType = xlDelimited
  95.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  96.         .TextFileConsecutiveDelimiter = False
  97.         .TextFileTabDelimiter = True
  98.         .TextFileSemicolonDelimiter = True
  99.         .TextFileCommaDelimiter = False
  100.         .TextFileSpaceDelimiter = False
  101.         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
  102.         .TextFileTrailingMinusNumbers = True
  103.         .Refresh BackgroundQuery:=False
  104.     End With
  105.     Selection.End(xlToRight).Select
  106.     Range("E11").Select
  107.     ActiveCell.FormulaR1C1 = "3"
  108.     Range("A1").Select
  109.     Selection.End(xlDown).Select
  110.     Range("A12").Select
  111.     With ActiveSheet.QueryTables.Add(Connection:= _
  112.         "TEXT;C:\WK44.Net\ABJ\MFS_list.csv" _
  113.         , Destination:=Range("A12"))
  114.         .Name = "MFS_list_4"
  115.         .FieldNames = True
  116.         .RowNumbers = False
  117.         .FillAdjacentFormulas = False
  118.         .PreserveFormatting = True
  119.         .RefreshOnFileOpen = False
  120.         .RefreshStyle = xlInsertDeleteCells
  121.         .SavePassword = False
  122.         .SaveData = True
  123.         .AdjustColumnWidth = True
  124.         .RefreshPeriod = 0
  125.         .TextFilePromptOnRefresh = False
  126.         .TextFilePlatform = 437
  127.         .TextFileStartRow = 1
  128.         .TextFileParseType = xlDelimited
  129.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  130.         .TextFileConsecutiveDelimiter = False
  131.         .TextFileTabDelimiter = True
  132.         .TextFileSemicolonDelimiter = True
  133.         .TextFileCommaDelimiter = False
  134.         .TextFileSpaceDelimiter = False
  135.         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
  136.         .TextFileTrailingMinusNumbers = True
  137.         .Refresh BackgroundQuery:=False
  138.     End With
  139.     Selection.End(xlToRight).Select
  140.     Range("E13").Select
  141.     ActiveCell.FormulaR1C1 = "4"
  142.     Range("E14").Select
  143.     ActiveCell.FormulaR1C1 = "4"
  144.     Range("A1").Select
  145. End Sub
  146.  
Thanks in anticipation
Nov 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: 9
Can anybody help me out.
Nov 17 '06 #2

100+
P: 267
Can anybody help me out.
hi you're using an extreme amount of statements if you ask me

e.g:
Range("E2").Select
ActiveCell.FormulaR1C1 = "1"
could be translated into 'real VBA' as
Range( "E2").Value="1"

Expand|Select|Wrap|Line Numbers
  1.     Range("A2").Select
  2.     Selection.End(xlToRight).Select
  3.     Range("E2").Select
  4.     ActiveCell.FormulaR1C1 = "1"
  5.     Range("E2").Select
  6.     Selection.Copy
  7.     Range("E3:E6").Select
  8.     ActiveSheet.Paste
  9.     Range("A7").Select
  10.  
what do you want in A2 ?

if you want to set range("e3:e6") to be "1"

for i=0 to 4
range("e3").offset(0,i).value="1"
next i


Expand|Select|Wrap|Line Numbers
  1.     Range("E8").Select
  2.     ActiveCell.FormulaR1C1 = "2"
  3.     Range("E9").Select
  4.     ActiveCell.FormulaR1C1 = "2"
  5.     Range("A1").Select
  6.     Selection.End(xlDown).Select
  7.     Range("A10").Select
  8.    With ActiveSheet.QueryTables.Add(Connection:= _
  9.         "TEXT;C:\WK44.Net\BEN\MFS_list.csv" _
  10.         , Destination:=Range("A10"))
  11.  
  12.  
same: Range("E8").Value="2"
range("e9").value to be "2" too ?
then you switch to A1 and back to A10 where you tell the program
to paste te next file from A10
double statement ...

by the way, you seem to know exactly how large the files are
while you use fixed addresses.
so why take the trouble for data-separation ?
Nov 17 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.