Here is the original code with added pop ups to select open and save files so
you don't have to modifiy code each time you want a new file name.

I work on the additional changes.

Private Sub OpenTextFile001()

fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fileToOpen, _
Destination:=Range("A1"))
.Name = "Sjoerd"
.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 = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub



Private Sub Format()
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "bla"
Range("B1").Select
ActiveCell.FormulaR1C1 = "blabla"
Range("C1").Select
ActiveCell.FormulaR1C1 = "blablabla"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "mysheet1"
Columns("B:B").Select
Selection.TextToColumns _
Destination:=Range("B1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True
End Sub


Private Sub Save001()
ChDir "A:\Anotherserver"
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

ActiveWorkbook.SaveAs _
Filename:=fileSaveName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub


Private Sub Nextfile()
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Product").Select
Sheets("Product").Name = "Sheet1"
End Sub


"Jumparound" wrote:

On 7 apr, 14:54, "Otto Moehrbach"
wrote:
Not sure of what you want. The row number of the last row of, say, Column A,
is:
RowNum=Range("A" & rows.count).end(xlup).row
If you have a header row and you don't want to count it in the number of
rows, you would subtract 1 from the RowNum. Does this help? Otto"Jumparound" wrote in message

...



Hello,

first of all, im not a programmer.
I do this to automate an very very boring task @ work.

This is what i do.
I import a text file into excel.
I add our layout to the sheet.
I write down the number of rows.
Then I save it with a new name.

this x 35.

Now, this is what i did in VBA (detail are not that important)

Call OpenTextFile001
Call Format
Call Save001
Call Nextfile
Call OpenTextFile002
Call Format
Call Save002
Call Nextfile

I made a macro of every step. This way somebody else can open the
macro and edit it with new information when im not at the office.

The only thing i havnt managed to solve is the Number of rows part.
I want to copy the numer of rows to a new workbook for each file I
process.
There is a very specific order in wich the files are processed, so all
I need in a new sheet would be a number.

Could someone help me with a SUB?- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Oke, thats what i want yes :-)
But how do i copy that value to a new workbook?

tnx!