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?

If you post the code we probably can add improvements

To get the last row use

LastRow = Range("A" & rows.count).end(xlup).row

Rows.Count for excel 2003 is 65536. It is a constant in excel. This code
says to go to the last row (65536) in column A and move up the worksheet
until data is found in a cell and then return the row number.

You are going to havve to creat a new workbook and then use a SAVEAS to save
the data. Not sure why you would want to put the number into a Workbook,
would be better as a text file.


"Jumparound" wrote:

> 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?
>

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" <(E-Mail Removed)> wrote in message
news:b9ee7b05-17c4-46c1-9ef0-(E-Mail Removed)...
> 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?

On 7 apr, 14:48, Joel <J...@discussions.microsoft.com> wrote:
> If you post the code we probably can add improvements
>
> To get the last row use
>
> LastRow = Range("A" & rows.count).end(xlup).row
>
> Rows.Count for excel 2003 is 65536. *It is a constant in excel. *This code
> says to go to the last row (65536) in column A and move up the worksheet
> until data is found in a cell and then return the row number.
>
> You are going to havve to creat a new workbook and then use a SAVEAS to save
> the data. *Not sure why you would want to put the number into a Workbook,
> would be better as a text file.
>
>
>
> "Jumparound" wrote:
> > 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 nietweergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Yes, the code could be improved, but i want that my collegue (who
doesnt work with VBA at all can copy paste my steps to add a new file
to the batch.

The reason that i want the number of rows in an excel file is that you
can copy paste it into our overview file (where the number of rows is
listed per period (so if there is a big difference between 2 periods,
there is something wrong with the output.). I dont know how to make
the code that it skips1 cell from the last input. (now that i think
about it. That would be even better)


The following code is all pretty basic, but it works AND i can explain
it to someone else :-)

Import the textfile

Private Sub OpenTextFile001()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Z:\textfile.txt", _
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

This is where the text if formatted to the way i want it


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

Save the file

Private Sub Save001()
ChDir "A:\Anotherserver"
ActiveWorkbook.SaveAs Filename:= _
"Anotherserver\mydestination\test.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Clear the contents for import of new datafile

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

On 7 apr, 14:54, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <sjoerd.j...@gmail.com> wrote in message
>
> news:b9ee7b05-17c4-46c1-9ef0-(E-Mail Removed)...
>
>
>
> > 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 nietweergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

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

tnx!

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" <moehrbachoex...@bellsouth.net>
> 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" <sjoerd.j...@gmail.com> wrote in message
> >
> > news:b9ee7b05-17c4-46c1-9ef0-(E-Mail Removed)...
> >
> >
> >
> > > 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!
>

Here is the routinne to save the last row. Again, I would save the file as
CSV which is a text file and an excel file. It would be only a few bytes of
text rather than a 20K file. The code below uses a pop up for the filename.
You can hardcode the filename is that is what you want.



Private Sub SaveLastRow()

LastRow = Range("A" & Rows.Count).End(xlUp).Row

'ChDir "A:\Anotherserver"
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
Workbooks.Add
Set newbook = ActiveWorkbook
newbook.Sheets("Sheet1").Range("A1") = LastRow
ActiveWorkbook.SaveAs _
Filename:=fileSaveName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
newbook.Close


End Sub

"Jumparound" wrote:

> On 7 apr, 14:54, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> 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" <sjoerd.j...@gmail.com> wrote in message
> >
> > news:b9ee7b05-17c4-46c1-9ef0-(E-Mail Removed)...
> >
> >
> >
> > > 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!
>

On 7 apr, 15:53, Joel <J...@discussions.microsoft.com> wrote:
> Here is the routinne to save the last row. *Again, I would save the fileas
> CSV which is a text file and an excel file. *It would be only a few bytes of
> text rather than a 20K file. *The code below uses a pop up for the filename. *
> You can hardcode the filename is that is what you want.
>
> Private Sub SaveLastRow()
>
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
>
> 'ChDir "A:\Anotherserver"
> fileSaveName = Application.GetSaveAsFilename( _
> * * fileFilter:="Excel Files (*.xls), *.xls")
> Workbooks.Add
> Set newbook = ActiveWorkbook
> newbook.Sheets("Sheet1").Range("A1") = LastRow
> ActiveWorkbook.SaveAs _
> * *Filename:=fileSaveName, _
> * *FileFormat:=xlNormal, _
> * *Password:="", _
> * *WriteResPassword:="", _
> * *ReadOnlyRecommended:=False, _
> * *CreateBackup:=False
> newbook.Close
>
> End Sub
>
>
>
> "Jumparound" wrote:
> > On 7 apr, 14:54, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> > 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" <sjoerd.j...@gmail.com> wrote in message
>
> > >news:b9ee7b05-17c4-46c1-9ef0-(E-Mail Removed)....
>
> > > > 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!- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

tnx guys!
I will look into it!
(maybe i wasnt exactely clear on 1 point. i just want to save the
number of rows for the batch i process
i.e.

Textfile001 30
Textfile002 24
Textfile003 112
Textfile004 22

ect. ect.