Example
Post #4 Copy table contents to VBE module (Part#4 Re Paste back to spreadsheet)
3) Copy table contents to VBE module
The idea of these codes are to allow for a temporary storage of spreadsheet values to a code module.
_ One code will add a table of values to the end of a code module, and an extra start and end line will be added which contains range and date info. This code uses the selected range. So a range must be selected before running this code.
and
_ a second code can be used to paste those values back to the same range.
Optionally a date of entry can be given to search for, otherwise all table values are pasted in
The codes are somewhat detailed and inefficient. They work in many places "line by line". There are some more simpler and more efficient codes here. http://www.eileenslounge.com/viewtop...=31395#p243002
Sub PubProliferous_Let_RngAsString__()
This code puts a selected range of values into the code module in which the code at the position of just after this coding. The table data values will be added to ( or taken from in following codes ) the module in which the code is run.
Here a brief walk through the code. There are more details in the 'comments
Rem 0 Sets for convenience, a variable to the code module in which the code is placed/ run from
Rem 1
A code module used for storing a table will be given the extension to its code name of "_txt" No special reason for doing this, I just thought it might be useful for later reference to know that the code module is being used in such a way. ( If no more table data is in the code module, then the extension , "_txt" , will be removed. This will be done , for example in following codes after the data is removed.)
Rem 2
Before running the code, a spreadsheet range should be selected. The range is copied to the clipboard, and the text of that put in a string variable, strIn
Rem 3
The string format is changes slightly to allow better display in this code ( and to aid in manipulation in the codes which re copy the data back to the spreadsheet, http://www.eileenslounge.com/viewtop...=31395#p242941
Rem 4
This adds an "extra line" to the start of the string with range information and current date, and an extra line at the end of the string with the same date. (I use date format of 10 digits as DD MM YYYY. ( This needs to be added to the data retrieving codes if looking for data from a specific date ) )
As demo example, say I copied a 2 row x 2 cell range, B2:C3 to the clipboard. Say the range looked like this
A B
C D
At this point in the routine, after Rem 4 , I will have in strIn , something of this approximate/pseudo form, ( say for a date of 12th December, 2018 ):
Rem 5Code:= " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") " & vbCr & vbLf & "|" & "A" & "|" & "B" & vbCr & vbLf & "|" & "C" & "|" & "D" & vbCr & vbLf & " '_-20 12 2018 "
The above string is converted to an array by splitting by the & vbCr & vbLf & . So effectively , I would end up with something of this form , for the above example , a 4 element, 1 Dimensional array:
{ SpltRws(0) , SpltRws(1) , SpltRws(2) , SpltRws(3) }
where
SpltRws(0) = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "
SpltRws(1) = "|" & "A" & "|" & "B"
SpltRws(2) = "|" & "C" & "|" & "D"
SpltRws(3) = " '_-20 12 2018 " "
Rem 6
We examine the dimensions of the array and determine which lines will be used at the end of the code module
Rem 7
'7a) '7d) The extra start and end lines are added as simple lines.
'7b) This is the main loop for inserting/(actually adding**) data lines
'7c) this second inner loop builds up the actual line to be outputted from the columns/cells: The row is first split into each cell/column , and then a string of all cell/columns rebuilt. This is done so that we can add some formatting to each cell/column. In this code we effectively add each cell/column string into a fixed length string variable, so that in the VB editor it looks a bit neater with the cell/columns being of equal with regardless of how many characters are in each cell/column
**Note: As discussed in the last post ( https://excelfox.com/forum/showthrea...ll=1#post15229 ) , we use the code line of this form:
___.insertlines Line:=____, String:=”____”
We actually use a value in the argument Line:=____ which is 1 greater than the last line. It is not possible to “Insert” a line where no line currently is. But for any value 1 or more greater than the last line, the code line appears to add** a new line. That line will then eventually / indirectly become the line number which is 1 greater than the last line number. So we achieve what we want, but are not doing it as directly as it might appear at first glance:- We can only insert lines at up to and including the last current line. If we try to insert anywhere above the last current line, then the ___.insertlines _ will add** a new line directly after the last current line.
_.___________________
Example of code run.
Consider that this spreadsheet range is highlighted,…._
_____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )
Worksheet: Tabelle1
Row\Col 24 11 2018 GH GT 2136.25 30 30.04 25 11 2018 GH GT 26 11 2018 GH GT 2150.17 51 51.1 27 11 2018 GH GT 2146.47 31 31
_... and then run the code Sub PubProliferous_Let_RngAsString__()
After doing this, you should see this towards the end of the code module in which the code is placed
You will note that you have lost a digit in the column A dates – the 8 in 2018 is missing. This is because the formatting is set to 9 characters by this variableCode:'7d) End row VBIDEVBAProj.insertlines Line:=CdTblStp, String:=SpltRws(UBound(SpltRws())) ' Note: this line would not go further than last line, so it must be done here *** End Sub '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") '_-24 11 201 | GH | GT | 2136.25 | 30 | 30.04 '_-25 11 201 | GH | GT | | | '_-26 11 201 | GH | GT | 2150.17 | 51 | 51.1 '_-27 11 201 | GH | GT | 2146.47 | 31 | 31 '_-21 12 2018
__TabulatorSyncrenator = "123456789" ' any lengthed string will do
You can overcome that problem by increasing the character length of that variable by 1 digit. Any character will do, for example
__TabulatorSyncrenator = "1234W56789"
Re run the code on the same range and you will now see this:
Why Tabulator SinkCranartor.JPG : https://imgur.com/i6VsFRP
( TabulatorSyncrenator = "12345Y6789" )
Code:End Sub '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") '_-24 11 201 | GH | GT | 2136.25 | 30 | 30.04 '_-25 11 201 | GH | GT | | | '_-26 11 201 | GH | GT | 2150.17 | 51 | 51.1 '_-27 11 201 | GH | GT | 2146.47 | 31 | 31 '_-21 12 2018 '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") '_-24 11 2018 | GH | GT | 2136.25 | 30 | 30.04 '_-25 11 2018 | GH | GT | | | '_-26 11 2018 | GH | GT | 2150.17 | 51 | 51.1 '_-27 11 2018 | GH | GT | 2146.47 | 31 | 31 '_-21 12 2018
Codes are here:
http://www.excelfox.com/forum/showth...0847#post10847
Note, the code line to clear the range is currently commented out
' rngSel.ClearContents ' range is cleared after copying table values to clipboard
_._____________________-
In the next posts we consider ways to Get at that data range in a code module, and re paste it into the spreadsheet at the original range
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Reply With Quote
Bookmarks