Content of a Worksheet to Notepad and Back

DenHueb

New Member
Joined
Feb 12, 2016
Messages
11
Hello,

could someone please help me out with a code that copies everything from one worksheet, creates a new notepad / text file, pastes everything into the notepad file, activates the worksheet and sets the format for all cells to text, opens the notepad file again and copies everything from there back to the worksheet?
After that the Notepad file can be closed without saving / deleting.

Many thanks,
Dennis
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
It sounds like you just want to convert formulas to values. If so, you can do that without using Notepad. Just select everything on your sheet, Copy it, then PasteSpecial --> values. We could create about a 2-line VBA macro if you want to do it that way.

Is this what you want to do?
 

DenHueb

New Member
Joined
Feb 12, 2016
Messages
11
There are no formulas, I just want everything to be in text format. I need the sheet in this format to be able to Import it into MS Project. The problem is, if I just change the format to text, it does not work properly.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
What does it do? I've tried the copy to Notepad, change Excel to Text, paste back technique and I get the same results as just changing the format. Is there a particular value that causes you a problem? Some large values have some issues.
 

DenHueb

New Member
Joined
Feb 12, 2016
Messages
11

ADVERTISEMENT

The problem are for example the dates which get changed to their codes (e.g. 42551). I want them in long format (U.K. Date) saved as text.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
OK, that makes sense. Try this on a copy of your workbook.

1) Open the workbook
2) Right click on the sheet tab on the bottom and select View Code
3) Paste this code into the window that opens
Code:
Sub SaveAsFormat()

    For Each cl In UsedRange
        MyFormat = cl.NumberFormat
        If IsNumeric(cl.Value) And MyFormat = "General" Then
            MyFormat = "General Number"
        End If
        mywork = Format(cl.Value, MyFormat)
        cl.NumberFormat = "@"
        cl.Value = mywork
    Next cl
    
End Sub
4) Press Alt-Q to close the VBA editor
5) Press Alt-F8 to open the macro selector
6) Select SaveAsFormat and click Run

That should do it. Make sure to test it on a COPY first. I haven't tested it on every possible format, which pretty much is impossible. It works on the formats I tried, but you may have something I didn't anticipate. My initial testing showed some odd results, and I don't want you to lose any data.

Let me know how this works.
 

DenHueb

New Member
Joined
Feb 12, 2016
Messages
11
The Code works perfectly, thank you very much!!

Could you please help me with the declaration of these variables?

Code:
        Dim cl As Range
        Dim MyFormat As Variant
        Dim mywork As Variant

Is this correct?

And just for understanding purposes:

For each cell in the used range of this worksheet, we save the format of the cell into the variable "MyFormat".
Then we check every cell if they have a "numeric cell value" and if their format is "General". If this two conditions are true we overwrite the variable "MyFormat" with the Format Code "General Number".

To be honest, I don't get why we need the if clause and what the next lines are doing.

Best regards,
Dennis
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
Yes, it is always a good idea to define your variables via a Dim statement. The definitions you propose are correct. (You could define mywork like this:
Dim mywork As String
See below.)

If you want to see why I had to change "General" to "General Number" for numeric values, just remove that line and see what happens. The formatting doesn't work right.

The Format function takes the cell value, and using the format we just determined, converts it into a string (text value).
Then we convert the cell Number format to Text ("@" is the code for text.)
And finally we put the text value we previously created back in the cell.
The order is important, because as you've seen, changing the format first can affect the contents.

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top