Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
I have a macro that is used to pull data from a data collection device
(winwedge). It is pulling three fields. It is a huge amount of data that will fill all 65536 rows within an hour. I would like to make it jump to the next set of columns when it reaches this point. Any help is greatly appriciated. Thanks, Bradley |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Bradley,
You would get better answers if you post your code.... Anyway, you could use something like If lngRow = 65000 Then CurCol = CurCol + 3 lngRow = 1 End If somewhere within your code. HTH, Bernie MS Excel MVP "iblonger" wrote in message ... I have a macro that is used to pull data from a data collection device (winwedge). It is pulling three fields. It is a huge amount of data that will fill all 65536 rows within an hour. I would like to make it jump to the next set of columns when it reaches this point. Any help is greatly appriciated. Thanks, Bradley |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Here is the code. I did not write this, it came from the manufacturer. I
know very little about what I'm looking at and am trying to learn. I did try putting what you gave me in and it didn not work. I am assuming that I made a mistake somewhere? Sub GetSWData() Static RowPointer As Long RowPointer = RowPointer + 1 Chan = DDEInitiate("WinWedge", "Com1") F1 = DDERequest(Chan, "Field(1)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 1).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(2)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 2).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(3)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$ DDETerminate Chan End Sub Thanks, Bradley "Bernie Deitrick" wrote: Bradley, You would get better answers if you post your code.... Anyway, you could use something like If lngRow = 65000 Then CurCol = CurCol + 3 lngRow = 1 End If somewhere within your code. HTH, Bernie MS Excel MVP "iblonger" wrote in message ... I have a macro that is used to pull data from a data collection device (winwedge). It is pulling three fields. It is a huge amount of data that will fill all 65536 rows within an hour. I would like to make it jump to the next set of columns when it reaches this point. Any help is greatly appriciated. Thanks, Bradley |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Bradley,
Try the version below. It will move to the next columns after 65000 rows. I used the RowPointer and some math to select the column and row to write to... HTH, Bernie MS Excel MVP Sub GetSWData2() 'Modified by Bernie Deitrick to wrap columns Static RowPointer As Long Const ROWCOUNT As Long = 65000 Chan = DDEInitiate("WinWedge", "Com1") F1 = DDERequest(Chan, "Field(1)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _ (RowPointer \ ROWCOUNT) * 3 + 1).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(2)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _ (RowPointer \ ROWCOUNT) * 3 + 2).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(3)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _ (RowPointer \ ROWCOUNT) * 3 + 3).Formula = WedgeData$ DDETerminate Chan RowPointer = RowPointer + 1 End Sub "iblonger" wrote in message ... Here is the code. I did not write this, it came from the manufacturer. I know very little about what I'm looking at and am trying to learn. I did try putting what you gave me in and it didn not work. I am assuming that I made a mistake somewhere? Sub GetSWData() Static RowPointer As Long RowPointer = RowPointer + 1 Chan = DDEInitiate("WinWedge", "Com1") F1 = DDERequest(Chan, "Field(1)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 1).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(2)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 2).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(3)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$ DDETerminate Chan End Sub Thanks, Bradley "Bernie Deitrick" wrote: Bradley, You would get better answers if you post your code.... Anyway, you could use something like If lngRow = 65000 Then CurCol = CurCol + 3 lngRow = 1 End If somewhere within your code. HTH, Bernie MS Excel MVP "iblonger" wrote in message ... I have a macro that is used to pull data from a data collection device (winwedge). It is pulling three fields. It is a huge amount of data that will fill all 65536 rows within an hour. I would like to make it jump to the next set of columns when it reaches this point. Any help is greatly appriciated. Thanks, Bradley |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
That worked great! THANKS SO MUCH FOR THE HELP.
"Bernie Deitrick" wrote: Bradley, Try the version below. It will move to the next columns after 65000 rows. I used the RowPointer and some math to select the column and row to write to... HTH, Bernie MS Excel MVP Sub GetSWData2() 'Modified by Bernie Deitrick to wrap columns Static RowPointer As Long Const ROWCOUNT As Long = 65000 Chan = DDEInitiate("WinWedge", "Com1") F1 = DDERequest(Chan, "Field(1)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _ (RowPointer \ ROWCOUNT) * 3 + 1).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(2)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _ (RowPointer \ ROWCOUNT) * 3 + 2).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(3)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _ (RowPointer \ ROWCOUNT) * 3 + 3).Formula = WedgeData$ DDETerminate Chan RowPointer = RowPointer + 1 End Sub "iblonger" wrote in message ... Here is the code. I did not write this, it came from the manufacturer. I know very little about what I'm looking at and am trying to learn. I did try putting what you gave me in and it didn not work. I am assuming that I made a mistake somewhere? Sub GetSWData() Static RowPointer As Long RowPointer = RowPointer + 1 Chan = DDEInitiate("WinWedge", "Com1") F1 = DDERequest(Chan, "Field(1)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 1).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(2)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 2).Formula = WedgeData$ F1 = DDERequest(Chan, "Field(3)") WedgeData$ = F1(1) Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$ DDETerminate Chan End Sub Thanks, Bradley "Bernie Deitrick" wrote: Bradley, You would get better answers if you post your code.... Anyway, you could use something like If lngRow = 65000 Then CurCol = CurCol + 3 lngRow = 1 End If somewhere within your code. HTH, Bernie MS Excel MVP "iblonger" wrote in message ... I have a macro that is used to pull data from a data collection device (winwedge). It is pulling three fields. It is a huge amount of data that will fill all 65536 rows within an hour. I would like to make it jump to the next set of columns when it reaches this point. Any help is greatly appriciated. Thanks, Bradley |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
You're quite welcome. Thanks for letting me know that it worked out OK for you.
Bernie MS Excel MVP That worked great! THANKS SO MUCH FOR THE HELP. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Now I need to try to add the date and time the data was captured. Can I do
this by just adding a line to the code? Thanks, Bradley "Bernie Deitrick" wrote: You're quite welcome. Thanks for letting me know that it worked out OK for you. Bernie MS Excel MVP That worked great! THANKS SO MUCH FOR THE HELP. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Bradley,
This will put the data and time into column 4 of the same row as your data.... Sheets("sheet1").Cells(RowPointer, 4).Value = Format(Now(), "mmm d, yyyy hh:mm") Put it in just after this line: Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$ HTH, Bernie MS Excel MVP "iblonger" wrote in message ... Now I need to try to add the date and time the data was captured. Can I do this by just adding a line to the code? Thanks, Bradley "Bernie Deitrick" wrote: You're quite welcome. Thanks for letting me know that it worked out OK for you. Bernie MS Excel MVP That worked great! THANKS SO MUCH FOR THE HELP. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Bernie,
Thanks for leading me in the right direction. I did have to change it a little using what you showed me previously with the rowpointer. See look at that not only did you help but you taught me something too. Thanks a million, Bradley "Bernie Deitrick" wrote: Bradley, This will put the data and time into column 4 of the same row as your data.... Sheets("sheet1").Cells(RowPointer, 4).Value = Format(Now(), "mmm d, yyyy hh:mm") Put it in just after this line: Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$ HTH, Bernie MS Excel MVP "iblonger" wrote in message ... Now I need to try to add the date and time the data was captured. Can I do this by just adding a line to the code? Thanks, Bradley "Bernie Deitrick" wrote: You're quite welcome. Thanks for letting me know that it worked out OK for you. Bernie MS Excel MVP That worked great! THANKS SO MUCH FOR THE HELP. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to move to next column
Bradley,
See look at that not only did you help but you taught me something too. Thanks a million, That's great, and you're quite welcome. Now you'll have to start answering posts here, too ;-) Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
Macro won't move in proper progression | Excel Discussion (Misc queries) | |||
Macro Help In Excel | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) |