Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster way to read data from old file to new
I am having to convert several files of data from an old DOS program and
read them into Excel. Each file has between 10,000 and 12,000 records of 229 bytes The records are all contiguous data within the files, with Chr(255)+Chr(255) determining the start of each new record. With Excel 2007, I was able to easily use DataText to Columns, setting Chr(255) as the delimiter and each record of 227 bytes (without the Chr(255)'s) was created in a separate column on row 1 of the file. A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139 rows (for the first file) each with 227 characters in column A. Each of these 227 byte records, is made up of 67 fields of varying lengths. These I have listed on another sheet with the length of each field and it's starting position. Whilst the short piece of code shown below does work and extracts all of the data into the relevant columns for me, I was wondering whether there was any faster way of effecting the conversion. I am using Vista SP1 and XL2007 SP1 Sub CreateRecords() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Dim i As Long, j As Long, lr As Long, start As Long, length As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wss = ThisWorkbook.Sheets("OldData") Set wsd = ThisWorkbook.Sheets("NewData") Set wst = ThisWorkbook.Sheets("Definition") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row For j = 2 To lr ' row 1 on Source is blank, row 1 on Destination is a Header row For i = 1 To 67 start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length) Next i Next j Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards Roger Govier |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster way to read data from old file to new
After you've transposed the data, you could record a macro that parses your data
(Data tab|Data tools group|text to columns). You'll have to separate & specify all 67 fields again, but once it's done, it's done. Well, until the layout changes! Maybe you could do all of it in one macro. Create a new workbook and start record when you open the file, copy|transpose, and data|text to columns. (And continue formatting (print headers & footers, column headers, freeze panes, autofilter, pivottables...) Then save that new workbook with a nice name: WorkbookToOpenParseAndFormatDOSOutput.xlsm And just open that whenever you need to do it again. Roger Govier wrote: I am having to convert several files of data from an old DOS program and read them into Excel. Each file has between 10,000 and 12,000 records of 229 bytes The records are all contiguous data within the files, with Chr(255)+Chr(255) determining the start of each new record. With Excel 2007, I was able to easily use DataText to Columns, setting Chr(255) as the delimiter and each record of 227 bytes (without the Chr(255)'s) was created in a separate column on row 1 of the file. A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139 rows (for the first file) each with 227 characters in column A. Each of these 227 byte records, is made up of 67 fields of varying lengths. These I have listed on another sheet with the length of each field and it's starting position. Whilst the short piece of code shown below does work and extracts all of the data into the relevant columns for me, I was wondering whether there was any faster way of effecting the conversion. I am using Vista SP1 and XL2007 SP1 Sub CreateRecords() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Dim i As Long, j As Long, lr As Long, start As Long, length As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wss = ThisWorkbook.Sheets("OldData") Set wsd = ThisWorkbook.Sheets("NewData") Set wst = ThisWorkbook.Sheets("Definition") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row For j = 2 To lr ' row 1 on Source is blank, row 1 on Destination is a Header row For i = 1 To 67 start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length) Next i Next j Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards Roger Govier -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster way to read data from old file to new
Hi Dave
Many thanks for the suggestion. I have just managed to get back to the problem this morning. The code I wrote worked absolutely fine, but naturally was very slow. My mind had become fixated on reading the data and chopping it into the appropriate length strings. The problem with Datatext to Columns, was that there are many fields which are empty. Trying to "eyeball" where the column dividers should be placed was very difficult, so I discarded that. However, following your suggestion about using a macro (also made to me by Debra in a private email -great minds think alike eh!!), I decided to record a macro on a copy set of data, letting Excel make it's own decision as to where the splits should be, then manually amend the macro with the field sizes as per the values in my Definition sheet and setting the range that is to be used for carrying out the conversion. The resulting code, which worked perfectly (and instantaneously) is shown below as it may help others approaching this type of problem Many thanks for "dragging" me out of my fixed way of looking at the problem. Sub ParseToColumns() Dim wss As Worksheet Dim lr As Long Set wss = ThisWorkbook.Sheets("OldData") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(2, 1), Cells(lr, 1)).Select Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _ FieldInfo:= _ Array(Array(0, 1), Array(14, 1), Array(17, 1), Array(23, 1), Array(26, 1), _ Array(35, 1), Array(38, 1), Array(47, 1), Array(50, 1), Array(53, 1), Array(54, 1), _ Array(57, 1), Array(67, 1), Array(77, 1), Array(79, 1), Array(81, 1), Array(91, 1), _ Array(92, 1), Array(95, 1), Array(105, 1), Array(108, 1), Array(111, 1), Array(113, 1), _ Array(117, 1), Array(119, 1), Array(121, 1), Array(123, 1), Array(125, 1), Array(127, 1), _ Array(130, 1), Array(132, 1), Array(133, 1), Array(135, 1), Array(137, 1), Array(140, 1), _ Array(143, 1), Array(145, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), _ Array(158, 1), Array(159, 1), Array(163, 1), Array(165, 1), Array(166, 1), Array(168, 1), _ Array(170, 1), Array(173, 1), Array(176, 1), Array(178, 1), Array(180, 1), Array(182, 1), _ Array(184, 1), Array(186, 1), Array(188, 1), Array(191, 1), Array(193, 1), Array(195, 1), _ Array(199, 1), Array(203, 1), Array(208, 1), Array(213, 1), Array(216, 1), Array(219, 1), _ Array(222, 1), Array(225, 1)) End Sub -- Regards Roger Govier "Dave Peterson" wrote in message ... After you've transposed the data, you could record a macro that parses your data (Data tab|Data tools group|text to columns). You'll have to separate & specify all 67 fields again, but once it's done, it's done. Well, until the layout changes! Maybe you could do all of it in one macro. Create a new workbook and start record when you open the file, copy|transpose, and data|text to columns. (And continue formatting (print headers & footers, column headers, freeze panes, autofilter, pivottables...) Then save that new workbook with a nice name: WorkbookToOpenParseAndFormatDOSOutput.xlsm And just open that whenever you need to do it again. Roger Govier wrote: I am having to convert several files of data from an old DOS program and read them into Excel. Each file has between 10,000 and 12,000 records of 229 bytes The records are all contiguous data within the files, with Chr(255)+Chr(255) determining the start of each new record. With Excel 2007, I was able to easily use DataText to Columns, setting Chr(255) as the delimiter and each record of 227 bytes (without the Chr(255)'s) was created in a separate column on row 1 of the file. A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139 rows (for the first file) each with 227 characters in column A. Each of these 227 byte records, is made up of 67 fields of varying lengths. These I have listed on another sheet with the length of each field and it's starting position. Whilst the short piece of code shown below does work and extracts all of the data into the relevant columns for me, I was wondering whether there was any faster way of effecting the conversion. I am using Vista SP1 and XL2007 SP1 Sub CreateRecords() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Dim i As Long, j As Long, lr As Long, start As Long, length As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wss = ThisWorkbook.Sheets("OldData") Set wsd = ThisWorkbook.Sheets("NewData") Set wst = ThisWorkbook.Sheets("Definition") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row For j = 2 To lr ' row 1 on Source is blank, row 1 on Destination is a Header row For i = 1 To 67 start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length) Next i Next j Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards Roger Govier -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster way to read data from old file to new
I don't think I'd trust excel to guess at the field lengths. But if it worked
ok for you, then congrats. I might have edited a text file (in notepad--or another text editor) and put a dummy record in near the top to help get the columns where I wanted them. Letting excel guess at anything always scares me. Roger Govier wrote: Hi Dave Many thanks for the suggestion. I have just managed to get back to the problem this morning. The code I wrote worked absolutely fine, but naturally was very slow. My mind had become fixated on reading the data and chopping it into the appropriate length strings. The problem with Datatext to Columns, was that there are many fields which are empty. Trying to "eyeball" where the column dividers should be placed was very difficult, so I discarded that. However, following your suggestion about using a macro (also made to me by Debra in a private email -great minds think alike eh!!), I decided to record a macro on a copy set of data, letting Excel make it's own decision as to where the splits should be, then manually amend the macro with the field sizes as per the values in my Definition sheet and setting the range that is to be used for carrying out the conversion. The resulting code, which worked perfectly (and instantaneously) is shown below as it may help others approaching this type of problem Many thanks for "dragging" me out of my fixed way of looking at the problem. Sub ParseToColumns() Dim wss As Worksheet Dim lr As Long Set wss = ThisWorkbook.Sheets("OldData") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(2, 1), Cells(lr, 1)).Select Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _ FieldInfo:= _ Array(Array(0, 1), Array(14, 1), Array(17, 1), Array(23, 1), Array(26, 1), _ Array(35, 1), Array(38, 1), Array(47, 1), Array(50, 1), Array(53, 1), Array(54, 1), _ Array(57, 1), Array(67, 1), Array(77, 1), Array(79, 1), Array(81, 1), Array(91, 1), _ Array(92, 1), Array(95, 1), Array(105, 1), Array(108, 1), Array(111, 1), Array(113, 1), _ Array(117, 1), Array(119, 1), Array(121, 1), Array(123, 1), Array(125, 1), Array(127, 1), _ Array(130, 1), Array(132, 1), Array(133, 1), Array(135, 1), Array(137, 1), Array(140, 1), _ Array(143, 1), Array(145, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), _ Array(158, 1), Array(159, 1), Array(163, 1), Array(165, 1), Array(166, 1), Array(168, 1), _ Array(170, 1), Array(173, 1), Array(176, 1), Array(178, 1), Array(180, 1), Array(182, 1), _ Array(184, 1), Array(186, 1), Array(188, 1), Array(191, 1), Array(193, 1), Array(195, 1), _ Array(199, 1), Array(203, 1), Array(208, 1), Array(213, 1), Array(216, 1), Array(219, 1), _ Array(222, 1), Array(225, 1)) End Sub -- Regards Roger Govier "Dave Peterson" wrote in message ... After you've transposed the data, you could record a macro that parses your data (Data tab|Data tools group|text to columns). You'll have to separate & specify all 67 fields again, but once it's done, it's done. Well, until the layout changes! Maybe you could do all of it in one macro. Create a new workbook and start record when you open the file, copy|transpose, and data|text to columns. (And continue formatting (print headers & footers, column headers, freeze panes, autofilter, pivottables...) Then save that new workbook with a nice name: WorkbookToOpenParseAndFormatDOSOutput.xlsm And just open that whenever you need to do it again. Roger Govier wrote: I am having to convert several files of data from an old DOS program and read them into Excel. Each file has between 10,000 and 12,000 records of 229 bytes The records are all contiguous data within the files, with Chr(255)+Chr(255) determining the start of each new record. With Excel 2007, I was able to easily use DataText to Columns, setting Chr(255) as the delimiter and each record of 227 bytes (without the Chr(255)'s) was created in a separate column on row 1 of the file. A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139 rows (for the first file) each with 227 characters in column A. Each of these 227 byte records, is made up of 67 fields of varying lengths. These I have listed on another sheet with the length of each field and it's starting position. Whilst the short piece of code shown below does work and extracts all of the data into the relevant columns for me, I was wondering whether there was any faster way of effecting the conversion. I am using Vista SP1 and XL2007 SP1 Sub CreateRecords() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Dim i As Long, j As Long, lr As Long, start As Long, length As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wss = ThisWorkbook.Sheets("OldData") Set wsd = ThisWorkbook.Sheets("NewData") Set wst = ThisWorkbook.Sheets("Definition") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row For j = 2 To lr ' row 1 on Source is blank, row 1 on Destination is a Header row For i = 1 To 67 start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length) Next i Next j Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards Roger Govier -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster way to read data from old file to new
It wasn't a case of trusting Excel to get the parsing correct.
It was just letting it create a macro, so that I could see the form it took for specifying the layout. Once I had that, I created the array from scratch, using the values that I already knew for field lengths. -- Regards Roger Govier "Dave Peterson" wrote in message ... I don't think I'd trust excel to guess at the field lengths. But if it worked ok for you, then congrats. I might have edited a text file (in notepad--or another text editor) and put a dummy record in near the top to help get the columns where I wanted them. Letting excel guess at anything always scares me. Roger Govier wrote: Hi Dave Many thanks for the suggestion. I have just managed to get back to the problem this morning. The code I wrote worked absolutely fine, but naturally was very slow. My mind had become fixated on reading the data and chopping it into the appropriate length strings. The problem with Datatext to Columns, was that there are many fields which are empty. Trying to "eyeball" where the column dividers should be placed was very difficult, so I discarded that. However, following your suggestion about using a macro (also made to me by Debra in a private email -great minds think alike eh!!), I decided to record a macro on a copy set of data, letting Excel make it's own decision as to where the splits should be, then manually amend the macro with the field sizes as per the values in my Definition sheet and setting the range that is to be used for carrying out the conversion. The resulting code, which worked perfectly (and instantaneously) is shown below as it may help others approaching this type of problem Many thanks for "dragging" me out of my fixed way of looking at the problem. Sub ParseToColumns() Dim wss As Worksheet Dim lr As Long Set wss = ThisWorkbook.Sheets("OldData") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(2, 1), Cells(lr, 1)).Select Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _ FieldInfo:= _ Array(Array(0, 1), Array(14, 1), Array(17, 1), Array(23, 1), Array(26, 1), _ Array(35, 1), Array(38, 1), Array(47, 1), Array(50, 1), Array(53, 1), Array(54, 1), _ Array(57, 1), Array(67, 1), Array(77, 1), Array(79, 1), Array(81, 1), Array(91, 1), _ Array(92, 1), Array(95, 1), Array(105, 1), Array(108, 1), Array(111, 1), Array(113, 1), _ Array(117, 1), Array(119, 1), Array(121, 1), Array(123, 1), Array(125, 1), Array(127, 1), _ Array(130, 1), Array(132, 1), Array(133, 1), Array(135, 1), Array(137, 1), Array(140, 1), _ Array(143, 1), Array(145, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), _ Array(158, 1), Array(159, 1), Array(163, 1), Array(165, 1), Array(166, 1), Array(168, 1), _ Array(170, 1), Array(173, 1), Array(176, 1), Array(178, 1), Array(180, 1), Array(182, 1), _ Array(184, 1), Array(186, 1), Array(188, 1), Array(191, 1), Array(193, 1), Array(195, 1), _ Array(199, 1), Array(203, 1), Array(208, 1), Array(213, 1), Array(216, 1), Array(219, 1), _ Array(222, 1), Array(225, 1)) End Sub -- Regards Roger Govier "Dave Peterson" wrote in message ... After you've transposed the data, you could record a macro that parses your data (Data tab|Data tools group|text to columns). You'll have to separate & specify all 67 fields again, but once it's done, it's done. Well, until the layout changes! Maybe you could do all of it in one macro. Create a new workbook and start record when you open the file, copy|transpose, and data|text to columns. (And continue formatting (print headers & footers, column headers, freeze panes, autofilter, pivottables...) Then save that new workbook with a nice name: WorkbookToOpenParseAndFormatDOSOutput.xlsm And just open that whenever you need to do it again. Roger Govier wrote: I am having to convert several files of data from an old DOS program and read them into Excel. Each file has between 10,000 and 12,000 records of 229 bytes The records are all contiguous data within the files, with Chr(255)+Chr(255) determining the start of each new record. With Excel 2007, I was able to easily use DataText to Columns, setting Chr(255) as the delimiter and each record of 227 bytes (without the Chr(255)'s) was created in a separate column on row 1 of the file. A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139 rows (for the first file) each with 227 characters in column A. Each of these 227 byte records, is made up of 67 fields of varying lengths. These I have listed on another sheet with the length of each field and it's starting position. Whilst the short piece of code shown below does work and extracts all of the data into the relevant columns for me, I was wondering whether there was any faster way of effecting the conversion. I am using Vista SP1 and XL2007 SP1 Sub CreateRecords() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Dim i As Long, j As Long, lr As Long, start As Long, length As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wss = ThisWorkbook.Sheets("OldData") Set wsd = ThisWorkbook.Sheets("NewData") Set wst = ThisWorkbook.Sheets("Definition") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row For j = 2 To lr ' row 1 on Source is blank, row 1 on Destination is a Header row For i = 1 To 67 start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length) Next i Next j Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards Roger Govier -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Faster way to read data from old file to new
Ahhh.
I thought you might have used the data on that other sheet to create the code that you needed. Or even used that data to create the array you needed. Roger Govier wrote: It wasn't a case of trusting Excel to get the parsing correct. It was just letting it create a macro, so that I could see the form it took for specifying the layout. Once I had that, I created the array from scratch, using the values that I already knew for field lengths. -- Regards Roger Govier "Dave Peterson" wrote in message ... I don't think I'd trust excel to guess at the field lengths. But if it worked ok for you, then congrats. I might have edited a text file (in notepad--or another text editor) and put a dummy record in near the top to help get the columns where I wanted them. Letting excel guess at anything always scares me. Roger Govier wrote: Hi Dave Many thanks for the suggestion. I have just managed to get back to the problem this morning. The code I wrote worked absolutely fine, but naturally was very slow. My mind had become fixated on reading the data and chopping it into the appropriate length strings. The problem with Datatext to Columns, was that there are many fields which are empty. Trying to "eyeball" where the column dividers should be placed was very difficult, so I discarded that. However, following your suggestion about using a macro (also made to me by Debra in a private email -great minds think alike eh!!), I decided to record a macro on a copy set of data, letting Excel make it's own decision as to where the splits should be, then manually amend the macro with the field sizes as per the values in my Definition sheet and setting the range that is to be used for carrying out the conversion. The resulting code, which worked perfectly (and instantaneously) is shown below as it may help others approaching this type of problem Many thanks for "dragging" me out of my fixed way of looking at the problem. Sub ParseToColumns() Dim wss As Worksheet Dim lr As Long Set wss = ThisWorkbook.Sheets("OldData") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(2, 1), Cells(lr, 1)).Select Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _ FieldInfo:= _ Array(Array(0, 1), Array(14, 1), Array(17, 1), Array(23, 1), Array(26, 1), _ Array(35, 1), Array(38, 1), Array(47, 1), Array(50, 1), Array(53, 1), Array(54, 1), _ Array(57, 1), Array(67, 1), Array(77, 1), Array(79, 1), Array(81, 1), Array(91, 1), _ Array(92, 1), Array(95, 1), Array(105, 1), Array(108, 1), Array(111, 1), Array(113, 1), _ Array(117, 1), Array(119, 1), Array(121, 1), Array(123, 1), Array(125, 1), Array(127, 1), _ Array(130, 1), Array(132, 1), Array(133, 1), Array(135, 1), Array(137, 1), Array(140, 1), _ Array(143, 1), Array(145, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), _ Array(158, 1), Array(159, 1), Array(163, 1), Array(165, 1), Array(166, 1), Array(168, 1), _ Array(170, 1), Array(173, 1), Array(176, 1), Array(178, 1), Array(180, 1), Array(182, 1), _ Array(184, 1), Array(186, 1), Array(188, 1), Array(191, 1), Array(193, 1), Array(195, 1), _ Array(199, 1), Array(203, 1), Array(208, 1), Array(213, 1), Array(216, 1), Array(219, 1), _ Array(222, 1), Array(225, 1)) End Sub -- Regards Roger Govier "Dave Peterson" wrote in message ... After you've transposed the data, you could record a macro that parses your data (Data tab|Data tools group|text to columns). You'll have to separate & specify all 67 fields again, but once it's done, it's done. Well, until the layout changes! Maybe you could do all of it in one macro. Create a new workbook and start record when you open the file, copy|transpose, and data|text to columns. (And continue formatting (print headers & footers, column headers, freeze panes, autofilter, pivottables...) Then save that new workbook with a nice name: WorkbookToOpenParseAndFormatDOSOutput.xlsm And just open that whenever you need to do it again. Roger Govier wrote: I am having to convert several files of data from an old DOS program and read them into Excel. Each file has between 10,000 and 12,000 records of 229 bytes The records are all contiguous data within the files, with Chr(255)+Chr(255) determining the start of each new record. With Excel 2007, I was able to easily use DataText to Columns, setting Chr(255) as the delimiter and each record of 227 bytes (without the Chr(255)'s) was created in a separate column on row 1 of the file. A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139 rows (for the first file) each with 227 characters in column A. Each of these 227 byte records, is made up of 67 fields of varying lengths. These I have listed on another sheet with the length of each field and it's starting position. Whilst the short piece of code shown below does work and extracts all of the data into the relevant columns for me, I was wondering whether there was any faster way of effecting the conversion. I am using Vista SP1 and XL2007 SP1 Sub CreateRecords() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Dim i As Long, j As Long, lr As Long, start As Long, length As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wss = ThisWorkbook.Sheets("OldData") Set wsd = ThisWorkbook.Sheets("NewData") Set wst = ThisWorkbook.Sheets("Definition") lr = wss.Cells(Rows.Count, "A").End(xlUp).Row For j = 2 To lr ' row 1 on Source is blank, row 1 on Destination is a Header row For i = 1 To 67 start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length) Next i Next j Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Regards Roger Govier -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
read data element from XML file | Excel Programming | |||
Read data from an htm file | Excel Programming | |||
Read data from anohter XLS file | Excel Worksheet Functions | |||
Read data from a text file (*.txt) | Excel Programming | |||
VBA to read data from XL and import into another XL file | Excel Programming |