Closing Notepad using VBA

J

jamesurwin

New Member
Joined
Feb 5, 2008
Messages
5
Hi,

I'm trying to write a macro that does the following:

1. Copy a range of cells from excel
2. Open Notepad
3. Paste, Select All and Copy
4. Return to excel worksheet and paste
5. Close Notepad

Could anyone advise me how you go about coding for closing Notepad please ?!?!?! I dont need to save .txt file once I've pasted back into excel.

Many thanks,
J
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Can you elaborate on what you are actually trying to achieve? It will almost certainly be easier not to involve Notepad at all (since it doesn't support automation)
 
Upvote 0
I have a table in excel approx 40 rows x 100 columns that includes integers and times that has been imported from a .pdf file. For whatever reason these numbers are not recognised properly unless I copy into Notepad and paste back into excel.

For example, I have a column of integers from 1 to 40 that are in no particular order. When I try to sort them in order it comes out as:

1
11
12
.
.
.
20
21
etc

I've tried formatting the cells in excel (to recognise them as a number instead of text etc) but it doesnt seem to make a difference. The only way I can make it work for me is if I copy and paste into Notepad, then paste back into Excel.

I'm in the process of writing the code to open notepad, and paste from excel, but I have no idea how to close notepad once i've copied the data back into excel.

Any help would be appreciated thanks, hope it makes sense.
 
Upvote 0
Copy a blank cell, select all the cells with 'numbers', goto Edit>Paste Special... and select Add from the Operation section.
 
Upvote 0
You could try code like this (select the cells first):
Code: 
Sub MakeNumeric()
    Dim rngCell As Range
    On Error GoTo err_handler
    With Application
       .Calculation = xlcalculationmanual
       .Screenupdating = False
    End With
     For Each rngCell In Selection
        With rngCell
            If .HasFormula Then
            '    .Formula = Replace(.Formula, "=", "=--")
            Else
                If Len(.Value) = 0 Then
                '    .Formula = vbNullString
                Else
                    If IsNumeric(.Value) Then .Value = Val(.Value)
                End If
            End If
        End With
    Next rngCell
    
clean_up:
    With Application
       .Calculation = xlcalculationautomatic
       .Screenupdating = True
    End With
    Exit Sub
    
err_handler:
    MsgBox "Error occurred converting values. Please check and try again"
    Resume clean_up
End Sub
 
Upvote 0
Winner Winner, Chicken Dinner !!

Both work a treat, thanks guys. I'll use the coding for this particular case, but Norie's tip will be very useful for other problems.

Thanks again,
J
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Opening a non TXT with Notepad in excel
Replies
8
Views
583
LS2021
L
sanantonio
  • Solved
VBA - Open a .csv in Notepad
Replies
4
Views
2K
sanantonio
sanantonio
J
  • Question
Run time error with VBA to copy Chart objects from closed workbook
Replies
4
Views
107
Domenic
D
T
  • Question
Run-time error '429': ActiveX component can't create object
Replies
0
Views
217
TheSardOz
T
KlausW
  • Solved
copy data from several closed workbooks to one workbook.
Replies
5
Views
681
KlausW
KlausW

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

Share this page

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
Back
Top