85,000 rows

[ratman]

My VBA macro in Excel will need to open an instance of Word, then via Word open an 8meg 85,000 row text file and delete the blank rows and save it as a text file.

If I get that far I can then fit it into Excel and take it from there.

Thanks people.

Ratman

 

[Dreamboat]

Sorry, Dave:

http://www.mvps.org/word/FAQs/InterDev/ControlXLFromWord.htm

http://www.mvps.org/word/FAQs/InterDev/ControlWordFromXL.htm

techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[Telsa]

Why not create a macro in Excel to delete the excessive rows?

I have a macro on my PC at work I created that will break the text file into two spreadsheets, delete the unneeded rows and then combine the whole thing into one spreadsheet.

It can be done... would you like to see my code???

Mary

 

[Dreamboat]

Mary: Yes, he would! I'm sure of it, and I would too. Thanks!
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[Telsa]

Okay then! You'll have to wait till I get to work on Monday though.

Mary

 

[Dreamboat]

Okay by me!
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[ratman]

both of you are very nice.....thx ...monday would be great

 

[Telsa]

Here it is... you'll will have to change the way it searches for blanks (maybe do a sort and then delete blanks). I wrote most of this by letting Excel write it.

Help it helps!

Sub DataImport()

' Open first data file
Workbooks.OpenText FileName:=DataFile, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Space:=True, _
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))

' Save first chunk of data file temporarily
ActiveWorkbook.SaveAs FileName:="TempInput.xls", _
FileFormat:=xlNormal

Application.Goto Reference:="R65536C1"

' If whole data file did not load then
If ActiveCell.Cells <> &quot;&quot; Then

' Open first data file to get any remaining data
' The STARTROW is the start of datafile being imported
Workbooks.OpenText FileName:=DataFile, _
Origin:=xlWindows, _
StartRow:=32767, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Space:=True, _
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))
ActiveWorkbook.SaveAs FileName:=&quot;TempHalf.xls&quot;, _
FileFormat:=xlNormal

Application.Goto Reference:=&quot;R32770C1&quot;
Rows(&quot;1:32770&quot.Select
Range(&quot;A32770&quot.Activate
Selection.Delete Shift:=xlUp

' Merge Data File
' Activate temp data file
Windows(&quot;TempInput.xls&quot.Activate

' Place cursor in upper region to select non-essential data
Range(&quot;A1&quot.Select
Selection.CurrentRegion.Select


' Delete the region of non-essential data
Selection.Delete Shift:=xlUp

' Move down to bottom of data pass first blank line in A1 then pass data
Selection.End(xlDown).Select
Selection.End(xlDown).Select

' Move cursor to empty cell pass data range
ActiveCell.Offset(1, 0).Range(&quot;A1&quot.Select

' Go to remaining data
Windows(&quot;TempHalf.xls&quot.Activate

' Get all data from second half of import
Selection.CurrentRegion.Select
Selection.Copy

' Paste into master data of first file
Windows(&quot;TempInput.xls&quot.Activate
ActiveSheet.Paste

' Select top empty row to delete
Rows(&quot;1:1&quot.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Windows(&quot;TempHalf.xls&quot.Activate
ActiveWorkbook.Close

Windows(&quot;TempInput.xls&quot.Activate
Range(&quot;A1&quot.Select

Else

' Place cursor in upper region to select non-essential data
Range(&quot;A1&quot.Select
Selection.CurrentRegion.Select

' Delete the region of non-essential data
Selection.Delete Shift:=xlUp

Range(&quot;A1&quot.Select

End If

End Sub

Mary