Copying unlimited range of data and closing the file

[morry]

Can someone tell me how to copy data from a csv file to a differen
sheet in a new excel workbook? My csv file is a log and there ar
61,000 rows of data that increases every day so I can't set a specifi
range. The way I have it coded just converts it to an excel file the
copies it to the new workbook as a new page then leaves the old cop
open. I am trying to open this file into a new workbook, close it, an
format the new excel sheet.

Here is what i have so far:

Workbooks.OpenText Filename:= _
"\\WRG4156\iLinkProe\batch\mxload.Proe.dat", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Other:=True
OtherChar:="|", FieldInfo:=Array(Array(1, 1), _
Array(2, 1))

'Sheets("mxload.proe").Copy Before:=Workbooks("Import Pro
Log.xls").Sheets(1)
'Worksheets("mxload.proe").Hide

Rows("1:2").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Part Number"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Letter State"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Sheet Size"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Release Level"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Sheet Count"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Engineer"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Decision Number"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Date Modified"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Unknown"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Part Name"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Cad Group"
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
End With
Columns("A:N").Select
Columns("A:N").EntireColumn.AutoFit
Columns("C:F").Select
Selection.EntireColumn.Hidden = True
Columns("H:I").Select
Selection.EntireColumn.Hidden = True
Columns("K:M").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select


If anyone can help with this, that would be great.

Thank you,
Morr

 

[Dave Peterson]

What happens if you uncomment this line:
'Sheets("mxload.proe").Copy _
Before:=Workbooks("Import ProE Log.xls").Sheets(1)
and change it to:
Sheets("mxload.proe").Move _
Before:=Workbooks("Import ProE Log.xls").Sheets(1)

(If you move the only sheet in a workbook somewhere else, then that workbook
will close.)

 

[morry]

Dave,

That helps with the closing but then it adds a new sheet everytim
the code runs, and renames the sheet (mxload.proe) to (mxload.proe (1)
and so on. I don't want to copy the whole sheet itself, just the dat
and I want to start it on row 3 so I can put headers on row 1 and 2.
The data gets larger every day so I can't set a range to be copied. D
you know how i can accomplish this?

Thank you
morr

 

[mudraker]

Morry


Try this modification to your code

I have merged some of your lines of code into the one line of code

I have also included a 2nd method for populating your headers




Sub ffff()
Dim GetBottomRow As Long
Dim wB As Workbook

Workbooks.OpenText FileName:= _
"\\WRG4156\iLinkProe\batch\mxload.Proe.dat", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Other:=True
OtherChar:="|", FieldInfo:=Array(Array(1, 1), _
Array(2, 1))
Set wB = ActiveWorkbook

GetBottomRow = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByRows
_
SearchDirection:=xlPrevious).Row
If GetBottomRow > Rows.Count - 2 Then
MsgBox "To Much Data"
End
End If
Rows("1:" & GetBottomRow).Copy
Workbooks("Import ProE Log.xls").Sheets(1).Range("a3").Paste
Rows("1:2").Insert Shift:=xlDown

'Range("A1").Value = "Part Number"
'Range("B1").Value = "Letter State"
'Range("C1").Value = "Type"
'Range("D1").Value = "Description"
'Range("F1").Value = "Sheet Size"
'Range("G1").Value = "Release Level"
'Range("H1").Value = "Sheet Count"
'Range("I1").Value = "Engineer"
'Range("J1").Value = "Decision Number"
'Range("K1").Value = "Date Modified"
'Range("L1").Value = "Unknown"
'Range("M1").Value = "Part Name"
'Range("N1").Value = "Cad Group"

Range("a1:n1") = Array("Part Number", "Letter State", "Type", _
"Description", "Sheet Size", "Release Level", "Sheet Count"
"Engineer", _
"Decision Number", "Date Modified", "Unknown", "Part Name", "Ca
Group")

Rows("1:1").Font.Bold = True
Rows("1:1").orizontalAlignment = xlCenter
Columns("A:N").EntireColumn.AutoFit
Columns("C:F").EntireColumn.Hidden = True
Columns("H:I").EntireColumn.Hidden = True
Columns("K:M").EntireColumn.Hidden = True
Range("A1").Select
wB.Close SaveChanges:=False
End Su

 

[morry]

Thank you for the code mudraker but when I ran it it gave me and erro
(object doesn't support this method) in the line listed below. I trie
to figure it out but I couldn't get anything else to work. Do you kno
whats wrong?

Workbooks("Import ProE Log.xls").Sheets(2).Range("a3").Paste


Dave - to clarify my objectives:

I have a csv log file that gets updated everyday. I also have a Exce
workbook called "Import ProE Log" I want to copy the data from the lo
file, delimit it, and open it as sheet two in my workbook. I need th
data two start on row three so I can insert headers on the first rows
I have my code in a command button so that this can be run every day.
Sorry it wasn't clear the first time.

Thank you both for your help

morr

 

[Dave Peterson]

That sounds like you throw away the previous data and start anew each day.

The new log file replaces the existing sheet 2 in your excel file.

I'm not sure what the name of the sheet2 is, but you could delete it first:

application.displayalerts = false
Workbooks("Import ProE Log.xls").Sheets("sheet2").delete
application.displayalerts = true

Then MOVE that imported worksheet to where you want it:

Activesheet.Move _
Before:=Workbooks("Import ProE Log.xls").Sheets(1)
activesheet.name = "Sheet2"

Right after you open that .csv file, it's the activesheet.

And it'll still be active when it hits the "import Proe log.xls" workbook.

Then..
With worksheets("sheet2")
.range("a1").resize(2,1).entirerow.insert
'to make room for the headers
end with

===================
Another option (if you aren't too confused already). Just copy that used range
to the existing worksheet starting in A3.

'clear out old data
Workbooks("Import ProE Log.xls").Sheets("sheet2").range("A3:IV65536").clear

Workbooks.OpenText Filename:= .....

activesheet.usedrange.copy _
destination:=Workbooks("Import ProE Log.xls").Sheets("sheet2").range("a3")

''''
and the headers won't need to be added.

============
Try changing mudraker's .paste line to: .PasteSpecial xlValues
or
Rows("1:" & GetBottomRow).Copy _
destination:=Workbooks("Import ProE Log.xls").Sheets(1).Range("a3")

(Yeah, there's lots of ways to skin that cat!)

Good luck,