Macro issue

  • Thread starter Thread starter alexm999
  • Start date Start date
A

alexm999

I setup a Macro that opens up a bunch of text file and imports them int
a CSV file and then data I need is copy and pasted into specific cells


Now the problem is that everytime i run the Macro, i get over 31 ope
CSV files. Is there a way i can add something to the script that wil
close the file automatically for me, but leave my main XLS file open?
 
Alex

presumably you are going around a loop to open and process the files? Why
not record a macro to close one of the CSV files and then incorporate this
code into your loop after you have processed the data ?

If you post an extract from your code (the loop to open and process the csv
files) I'm sure someone will draft some code for you.

Regards

Trevor
 
Alex,

Something like

Workbooks.Open Filename:="C:\myfile.txt"
' do your thing
Activeworkbook.Close

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Here's the code:

what can i add to get the file 2.txt to close at the end?

Sub macro1()
Workbooks.OpenText Filename:="E:\UDC\2.TXT", Origin:=xlWindows,
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1))
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C8").Select
ActiveSheet.Paste
Range("E8").Select
Windows("2.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J8").Select
Windows("2.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K8").Select
Windows("2.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI8").Select
Windows("2.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
 
Alex,

Try this

Sub macro1()
Dim oThisFile As Workbook '<<<<<<<<<<<<<<<<<<<< New code
Workbooks.OpenText Filename:="E:\UDC\2.TXT", Origin:=xlWindows,
Set oThisFile As ActiveWorkbook '<<<<<<<<<<<<<<<<<<<< New code
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1))
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C8").Select
ActiveSheet.Paste
Range("E8").Select
Windows("2.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J8").Select
Windows("2.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K8").Select
Windows("2.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI8").Select
Windows("2.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste

oThisFile.Close '<<<<<<<<<<<<<<<<<<<< New code

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top