Copy table contents to VBE module

[YasserKhalil]

Hello everyone
I am trying to copy the contents of a table to VBE module manually .. and paste the contents of the table to the module
Then when trying to copy those contents and put it into the worksheet I got incorrect results ..
What is the best way to do such a process?

[HansV]

Why do you want to paste a table into a code module?

[YasserKhalil]

I would store it for some time while I will clear it into the worksheet ...

[HansV]

I'd store it on the clipboard, or on a temporary sheet. The VBE is not intended for storing cells.

[YasserKhalil]

Thanks a lot Mr. Hans
I have solved that by copying column by column and that is easier for me now to restore the data back to the worksheet
Regards

[Doc.AElstein]

..trying to copy the contents of a table to VBE module and paste the contents of the table to the module….. I got incorrect results …….solved that by copying column by column

It seems that the problem lies in that
_ the string held in the clipboard ( for rows ) has vbTabs in it which Excel seems to recognise as the “cell break” or "cell wall" or similar...,
but
_ the Text in the code module appears to change those to some sort of spaces characters.

When you copy a range ( of more than 1 column ) to the clipboard, paste that into a code module, and then try to copy from the code module and repaste into the range, it appears that Excel reads those spaces as spaces in a single text.
So, for example, if you copy range A1:C1 and past in a code module, you then find on copying from the code module back into the worksheet that the values from the cells end up in the same cell with a few spaces between them.

I wrote some routines and had an experiment with this test range:
RangeA1_C3.JPG : https://imgur.com/QiMqQUO" onclick="window.open(this.href);return false;

RangeA1_C3.JPG

Sub WotchaGotInHorizontalClip() takes a look at what is in the clipboard after Range("A1:C1").Copy, and then adds that to the code module.
You see that it looks something like this under examination:
"A" & "1" & vbTab & "B" & "1" & vbTab & "C" & "1" & vbCr & vbLf
In the code module it looks like this ( I add Rems just to stop the code window showing a red error , so ignore any of those )
RangeA1_C1 in code module.JPG : https://imgur.com/e4GKkry" onclick="window.open(this.href);return false;

Sub WotchaGotInCodeWindowHorizontal() takes a look at what that string looks like in the code module, then pastes that back into the worksheet
You see that it looks something like this under examination:
"A" & "1" & " " & " " & " " & "B" & "1" & " " & " " & "C" & "1"
After repasting you end up with this:
RangeA1_C1 Repaste in Worksheet.JPG : https://imgur.com/ab9LVJW" onclick="window.open(this.href);return false;

Oh Poo :(

Sub WotchaGotInVirticalClip and Sub WotchaGotInCodeWindowVertical() do similar experiments to examine what happens in an attempt to copy column A1:A3 to the code module, then back to the worksheet
We find that.._
_.. In the clipboard initially we have like:
"A" & "1" & vbCr & vbLf & "A" & "2" & vbCr & vbLf & "A" & "3" & vbCr & vbLf
_.. The string held in the code module looks like:
"A" & "1" & vbCr & vbLf & "A" & "2" & vbCr & vbLf & "A" & "3" & vbCr & vbLf
Unless I am mistaken they are identical, and we find that all is well for the case of columns

_._____________________________

I though it would be useful to take this further as I have a file with spare Class object modules that nobody else has , but that I could not find any use for as codes won’t work in them….


So, this routine, Sub Public_Properly_Let_RngAsString_() puts the range A1:C3 into a spare code module.
The corresponding routine Sub Fumic_Properly_Get_Rng_AsString() puts the range back into the worksheet.

What I do basically is replace the vbTab with a pipes, “ | “ , in the code module, and then change those pipes back to vbTabs before pasting back into the worksheet.

Alan

Codes here ( and in attatched file ) : https://pastebin.com/R5P5z35T" onclick="window.open(this.href);return false; , http://www.excelfox.com/forum/showthrea ... ix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-" onclick="window.open(this.href);return false;)?p=10841#post10841 , http://www.excelfox.com/forum/showthrea ... ix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-" onclick="window.open(this.href);return false;)?p=10840#post10840 , https://tinyurl.com/y7lwmzk3" onclick="window.open(this.href);return false; , https://tinyurl.com/ycmz2gab" onclick="window.open(this.href);return false;
( Notes:
_ when you paste them in leave a space at the top of the module for the pasted in range
_ For the last two routines , Sub Public_Properly_Let_RngAsString_() , Sub Fumic_Properly_Get_Rng_AsString() , you will need to change the referred to code name from "Tabelle1" to any spare code module you have. - If you choose an existing one with coding in it, then make sure you clear a space at the top of the module.





Ref
https://www.spreadsheet1.com/how-to-cop ... l-vba.html" onclick="window.open(this.href);return false;

[YasserKhalil]

Thank you very much Mr. Alan for the great effort
But to be honest I am lost and couldn't get how to make use of the file and the code .. I see you have hard-coded the values into the code

[Doc.AElstein]

Hi Yasser,
The first 4 codes were just intended to demonstrate the issues.
They show you that the string held in the clipboard after a copy of a columns and that string held in the code module are the same. Hence no problems there.
For the case of a row, the string held in the clipboard after a copy of a row , and the string held in the code module for that row are different. Hence the problem with a range containing more than 1 column.

The final 2 codes show an example of how to overcome the problem.
Sub Public_Properly_Let_RngAsString_() copies a 3x3 range into a code module.
Sub Fumic_Properly_Get_Rng_AsString() copies the same range from that code module back into the worksheet.

It would not be difficult to modify the codes to copy any range to a code module and then paste it back in. That might be a useful code to have. I like the idea. If I do that sometime then I will add it to the thread.
I personally use the code module a great deal for large amounts of ‘comment text. If you scroll to the right in the code module you have lots of space there. I think I like the idea of mis using the code window to copy ranges. I think I may do it in the future a lot.
But I am probably slightly insane. Some people think so, ( Doctors, physiologists, experts in the field of mental health). Intelligent people like Hans will probably advise against such things, and I highly expect they are right.
But I think sanity is over rated myself, but that is just my opinion. :-)

Alan

[YasserKhalil]

Thank you very much

I have copied these two subs to standard module ... but I got error 'Sub or Function not defined ..'
at this line
Paste Destination:=Range("A1")

Will I have to use that in worksheet module?

[Doc.AElstein]

Usually I qualify things fully, but I was a bit careless with these codes.
Yes I was mostly using a worksheet code module.
There are a few things which rely on the demo codes being in a worksheet code module, so it is probably better to run those in a worksheet code module initially, ( as they are in the uploaded file).
It is probably better to use my file until you understand the codes. Once you understand the codes then it will be more obvious where you need to better fully qualify ranges etc..

[YasserKhalil]

Thanks a lot
Is it possible to edit the last code of 'Get' to be used in standard module ...?

[Doc.AElstein]

I have to go off the computer now. If you upload a file with a test range, then I will write two codes for you , probably tomorrow:
_ One code will copy that range from the spreadsheet to a code module, ( then clear the range from the spreadsheet )
_ The other code will copy the range from the code module back to the original spreadsheet range, ( then clear the range from the code module )

I will put both codes in a normal code module ... and put lots of pretty 'comments om it :-)

Alan

(P.S. there were a few typos in the uploaded file which I have corrected, but it would not have effected the coding)

[YasserKhalil]

Thanks a lot for your interest ...
No specific file needed .. Your file is OK for me (All what I need is to be able to use it through standard module)
Take your time. I am not in hurry
Best and Kind Regards

[Doc.AElstein]

I have written the two codes. I have placed them both in two files which I have uploaded. In both files the codes are in a normal code module, “YassersCodMod”
The Normal Excel File also uses a normal code module for the code module range data storage, “YassersDump”
My File uses one of the extra new Worksheet Class worksheet code modules, “Tabelle1”

I have made one new addition to the basic coding idea from the last posts: This came about as I had problems with global variables being emptied by the code lines which reference the VB Project. In any case Global variable are Poo anyway http://www.eileenslounge.com/viewtopic. ... 52#p229490" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 52#p229669" onclick="window.open(this.href);return false;
So a simple Header is included now in the stored range info. That seems quite a good idea anyway
RangeIdentCodeModuleStorage.JPG : https://imgur.com/TCgLeV0" onclick="window.open(this.href);return false;
The Let code adds that range identification to the stored range data in the VB code module.
The Get code uses that information to determine the range in which to re paste the stored range data

So there are two codes in normal code module “YassersCodMod”, Private Sub Publics_Probably_Let_ RngAsString__() and Private Sub Publics_Probably_Get_Rng__AsString()
How to do the demo:…
_1)
You select a range, and then run the Let code. ( I use range C8:E14 , https://imgur.com/hUbgrar" onclick="window.open(this.href);return false; , but you can use any range ). You must select a range before you run the Let code

After running that code you should see this in the code module used for temporary data range value storage : https://imgur.com/pONBWv5" onclick="window.open(this.href);return false;
For the normal Excel File you should see that in code module “YassersDump”
For my file, you should see that in Worksheet Class worksheet code module, “Tabelle1”. ( I am using this just because I am trying to get familiar with the vague concepts of class and class objects and the preferences of where professionals store their codes which is often based on an attempt to “fit in” with the vague Object Orientated Programming concepts )
( The demo code clears the range from the spreadsheet, rngSel.ClearContents )

_2) Run the Get code. That should put the spreadsheet range values previously cleared back in. It gets the data from the code module used for the data range value storage. Finally, this code deletes the data range value from the code module used for the data range value storage

Alan

_.___

P.S.1. I currently have a daily back up log that I keep on a text file which looks something like this.
TextDailyBackUp.JPG : : https://imgur.com/HJUNEHJ" onclick="window.open(this.href);return false;
Using a spare code window to do the same is an interesting alternative
Also it is an interesting alternative to using extra worksheets for simple data, such as global variable values
It is also an interesting form or “Private storage” for those sharing files to people who are unfamiliar with VBA. You could “hide” information way to the right in the VB code module window
So I may look at this further some time in the future..._
_..but …..
P.S.2. This is all new stuff to me. I have never seen it done before. I expect it is a bad idea. I like it. But use it at your own risk :-)

_._____________________________________________________
The full codes including lots of explaining ‘comments are in the files, and here: https://tinyurl.com/ycmz2gab" onclick="window.open(this.href);return false; , https://pastebin.com/FCK5BALw" onclick="window.open(this.href);return false;
Here is a shortened pair of codes, ( to use normal code module “YassersDunp” for temporary range value storage )

Private Sub Public_Probably_Let_RngAsString__()

Code: Select all

Private Sub Public_Probably_Let_RngAsString__()
Dim rngSel As Range: Set rngSel = Selection: rngSel.Copy
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 objDataObject.GetFromClipboard
Dim strIn As String: strIn = objDataObject.GetText(): rngSel.ClearContents
 strIn = "'_-" & Replace(Replace(strIn, vbTab, " | "), vbLf, vbLf & "'_-")
 strIn = "'_-Worksheets(""" & rngSel.Parent.Name & """).Range(""" & rngSel.Address & """)" & vbCrLf & strIn
 On Error Resume Next
 ThisWorkbook.VBProject.VBComponents("YassersDump").CodeModule.AddFromString strIn
End Sub

Private Sub Public_Probably_Get_Rng__AsString()

Code: Select all

Private Sub Public_Probably_Get_Rng__AsString()
Dim strVonCodMod As String
Dim Ws As Worksheet, Rng As Range
 strVonCodMod = ThisWorkbook.VBProject.VBComponents("YassersDump").CodeModule.Lines(Startline:=1, Count:=1)
 strVonCodMod = Replace(Replace(Replace(strVonCodMod, "'_-Worksheets(""", ""), """).Range(""", " "), """)", "")
 Set Ws = Worksheets(Split(strVonCodMod)(0)): Set Rng = Ws.Range(Split(strVonCodMod)(1))
 strVonCodMod = ThisWorkbook.VBProject.VBComponents("YassersDump").CodeModule.Lines(Startline:=2, Count:=Rng.Rows.Count + 1)
 strVonCodMod = Replace(Replace(strVonCodMod, "'_-", ""), " | ", vbTab)
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 objDataObject.SetText strVonCodMod: objDataObject.PutInClipboard
 Ws.Paste Destination:=Rng
 On Error Resume Next
 ThisWorkbook.VBProject.VBComponents("YassersDump").CodeModule.DeleteLines Startline:=1, Count:=Rng.Rows.Count + 1 + 1
End Sub

[YasserKhalil]

That's great Mr. Alan
Thank you very much for your great and awesome efforts in that issue. That solved the issue completely
Best Regards