Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
issues copying data from one file to another
Hello, I'm trying to copy data from one file to another. The data is copied
almost "as is" except the columns are essentially rearranged. I'm doing it using the following code (as opposed to copying & pasting whole columns one at a time) because I want to be able to skip some rows that don't actually contain data. I have 3 questions based on the code excerpt below ("..." = code not shown). Please keep in mind the source file was opened using a macro from the destination file and the source file is presently active: .... for j = 1 to sourceLastCell.Row ' the last row in the source file ... entryA = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColA) ' A is a number formatted as "00000000", i.e. leading zeros if not 8 digits long entryB = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColB) ' B is alphanumeric ... ' the reason I pull the values into entryA & entryB first is that I need to ' determine if I need to skip copying the row of data ' destRange points to a single cell in the destination file ... destRange.Offset(k,destColA).Value = format(entryA,"00000000") destRange.Offset(k,destColB).Value = entryB ... k = k + 1 ' row index in the destination file next j .... This code works insofar as the data I want copied are all copied to the right cells. However my 3 questions a 1) All the data copied are effectively "copied with formats" but I want effectively to "copy & paste values". Is there a way to modify this code so the cell formats are not copied to the destination? 2) the Format() code does not do what I expect it to do. In other words, a number with fewer than 8 digits are copied without the leading zeros I'm trying to force onto it. Is there a way to make sure the leading zeros are there? 3) Compared copying whole columns one at a time, this method because it goes to the files for each individual cell takes considerably longer time to execute. I am in debug mode doing just ~6,000 rows at one time with ~13 columns/cells to copy for each row. My code is completing the ~6,000 rows in 1min compared to the straight whole column copy/paste which takes <5sec (all times rough estimates). I need to scale this eventually to ~15,000 rows. Is there a smarter/faster way to do this? Thanks a lot. Rockhammer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
issues copying data from one file to another
#1. The code you posted doesn't copy formats. It just assigns values. These
two lines do the work: destRange.Offset(k,destColA).Value = format(entryA,"00000000") destRange.Offset(k,destColB).Value = entryB #2. When you type 0000003 into a cell that's formatted as General, excel sees what you're typing and knows it's just 3. Same thing when you do the "typing" in code. You could change the numberformat of that cell: with destRange.Offset(k,destColA) .numberformat = "00000000" .Value = entryA end with Or you could force the cell to be text: with destRange.Offset(k,destColA) .numberformat = "@" .Value = format(entryA,"00000000") end with or more simply: destRange.Offset(k,destColA).Value = "'" & format(entryA,"00000000") #3. Maybe you can exclude the rows you don't want to copy by applying data|Filter|autofilter and hiding those rows that should be ignored. The copy|paste the visible cells???? rockhammer wrote: Hello, I'm trying to copy data from one file to another. The data is copied almost "as is" except the columns are essentially rearranged. I'm doing it using the following code (as opposed to copying & pasting whole columns one at a time) because I want to be able to skip some rows that don't actually contain data. I have 3 questions based on the code excerpt below ("..." = code not shown). Please keep in mind the source file was opened using a macro from the destination file and the source file is presently active: ... for j = 1 to sourceLastCell.Row ' the last row in the source file ... entryA = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColA) ' A is a number formatted as "00000000", i.e. leading zeros if not 8 digits long entryB = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColB) ' B is alphanumeric ... ' the reason I pull the values into entryA & entryB first is that I need to ' determine if I need to skip copying the row of data ' destRange points to a single cell in the destination file ... destRange.Offset(k,destColA).Value = format(entryA,"00000000") destRange.Offset(k,destColB).Value = entryB ... k = k + 1 ' row index in the destination file next j ... This code works insofar as the data I want copied are all copied to the right cells. However my 3 questions a 1) All the data copied are effectively "copied with formats" but I want effectively to "copy & paste values". Is there a way to modify this code so the cell formats are not copied to the destination? 2) the Format() code does not do what I expect it to do. In other words, a number with fewer than 8 digits are copied without the leading zeros I'm trying to force onto it. Is there a way to make sure the leading zeros are there? 3) Compared copying whole columns one at a time, this method because it goes to the files for each individual cell takes considerably longer time to execute. I am in debug mode doing just ~6,000 rows at one time with ~13 columns/cells to copy for each row. My code is completing the ~6,000 rows in 1min compared to the straight whole column copy/paste which takes <5sec (all times rough estimates). I need to scale this eventually to ~15,000 rows. Is there a smarter/faster way to do this? Thanks a lot. Rockhammer -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
issues copying data from one file to another
Hi Dave, thanks for your suggestions.
Your suggestion for #2 works very well. Yes, I neglected that it's the destination that needs the formatting, not the source. As to #1, I agree with you that the code I have should not copy formats - and perhaps I should qualify what I meant with the observation that NOT ALL formats were copied (e.g. background shading, font colour, etc., were not copied). However, I had formatted my destination cells to "centre justify" and after running the code, none of the cells with values assigned retained the justification format and all became (what looks like) "general" cell format, i.e. text aligned to the left, numbers to the right, and nothing centre justified. It's weird, or at least I can't explain it. As to #3, the reason I need to skip some of the rows is that after copying the data I am going to do some percentile calculations on some of the columns. But the data has a lot of straggling cells with things like totals, averages, excess zeroes, in between the actual data that I need to get rid of before I can run the calculations. So hiding it doesn't serve the purpose. I can do the straight copy/paste individual columns and then in a second pass go row by row to figure out which I need to clear and then sort the result to get there. That might be faster. At the end of the day, the cell justification and speed issues I can live with. The critical problem was the format() one which you helped me solve. So thanks a lot. Rockhammer "Dave Peterson" wrote: #1. The code you posted doesn't copy formats. It just assigns values. These two lines do the work: destRange.Offset(k,destColA).Value = format(entryA,"00000000") destRange.Offset(k,destColB).Value = entryB #2. When you type 0000003 into a cell that's formatted as General, excel sees what you're typing and knows it's just 3. Same thing when you do the "typing" in code. You could change the numberformat of that cell: with destRange.Offset(k,destColA) .numberformat = "00000000" .Value = entryA end with Or you could force the cell to be text: with destRange.Offset(k,destColA) .numberformat = "@" .Value = format(entryA,"00000000") end with or more simply: destRange.Offset(k,destColA).Value = "'" & format(entryA,"00000000") #3. Maybe you can exclude the rows you don't want to copy by applying data|Filter|autofilter and hiding those rows that should be ignored. The copy|paste the visible cells???? rockhammer wrote: Hello, I'm trying to copy data from one file to another. The data is copied almost "as is" except the columns are essentially rearranged. I'm doing it using the following code (as opposed to copying & pasting whole columns one at a time) because I want to be able to skip some rows that don't actually contain data. I have 3 questions based on the code excerpt below ("..." = code not shown). Please keep in mind the source file was opened using a macro from the destination file and the source file is presently active: ... for j = 1 to sourceLastCell.Row ' the last row in the source file ... entryA = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColA) ' A is a number formatted as "00000000", i.e. leading zeros if not 8 digits long entryB = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourc eColB) ' B is alphanumeric ... ' the reason I pull the values into entryA & entryB first is that I need to ' determine if I need to skip copying the row of data ' destRange points to a single cell in the destination file ... destRange.Offset(k,destColA).Value = format(entryA,"00000000") destRange.Offset(k,destColB).Value = entryB ... k = k + 1 ' row index in the destination file next j ... This code works insofar as the data I want copied are all copied to the right cells. However my 3 questions a 1) All the data copied are effectively "copied with formats" but I want effectively to "copy & paste values". Is there a way to modify this code so the cell formats are not copied to the destination? 2) the Format() code does not do what I expect it to do. In other words, a number with fewer than 8 digits are copied without the leading zeros I'm trying to force onto it. Is there a way to make sure the leading zeros are there? 3) Compared copying whole columns one at a time, this method because it goes to the files for each individual cell takes considerably longer time to execute. I am in debug mode doing just ~6,000 rows at one time with ~13 columns/cells to copy for each row. My code is completing the ~6,000 rows in 1min compared to the straight whole column copy/paste which takes <5sec (all times rough estimates). I need to scale this eventually to ~15,000 rows. Is there a smarter/faster way to do this? Thanks a lot. Rockhammer -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data from multiple file into one file | Excel Discussion (Misc queries) | |||
Copying data to from one file to another file | Excel Programming | |||
Copying & Filling in data from One file into another. | Excel Worksheet Functions | |||
copying data from csv file | Excel Programming |