Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
On 7 apr, 14:48, Joel 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 niet weergeven - - 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
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" 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows, post result in new book
On 7 apr, 15:53, Joel wrote:
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" 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!- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Search, Count, Match and Post Values | Excel Worksheet Functions | |||
Can I link a result from Quick Book Pro 2006 to an Excel file? | Excel Discussion (Misc queries) | |||
Can I link a result from Quick Book Pro 2006 to an Excel file? | Excel Discussion (Misc queries) | |||
Can I link a result from Quick Book Pro 2006 to an Excel file? | Excel Discussion (Misc queries) | |||
Wrong result returned by UsedRange.Rows.Count | Excel Programming |