Posts by nojy

Re: Closing Down

Works a treat thanks!

Re: Closing Down

Thanks, how could I get it to close down Excel completely?

Re: Closing Down

Thats great thanks!

Hello,

Can anyone advise what piece of vba code is required to close down a worksheet automatically that has just run a macro?

Thanks very much,

Jon

Re: Ping a Computer

I'm having trouble with the ping code linked above.

One of the servers i'm pinging seems to keep returning a false result even though it is always live?

I've run a ping-t and it runs without any timeouts, however the replies are a little slow.

Can anyone advise how this code can be changed to wait a little long before reporting False.

Thanks,

Jon Hardman

Re: String Split in vba

works great thanks!

Re: String Split in vba

That works a treat thanks, how could I find the length of the variable?

Thanks.

Hi,

I've looked at several threads on the forum relating to spliting a string, however there not quite answering the problem I have:

Within VBA I have a string that is actually taken from a cell, this could be any length and is a list of usernames seperated by a coma.

What I need to be able to do is split this string into an array, with each user name appearing in a different element of the array, i.e:

OrginalString = Fred,Smith

Split down to:

String(0) = Fred
String(1) = Smith

Does this make sense, any suggestions would be greatly recieved,

Thanks

Jon Hardman

Re: Net Send

Excellent thanks for the speedy reply, how easy was that.

Just needed the know!

Net send code works fine.

Thanks,

Jon

Hi,

I am trying to net send a message through excel, I am using the following bit of code:

Option Explicit




Sub ExecuteNetSend()




Dim objShell As New WshShell
Dim objExec As WshExec
Dim strComputer As String
Dim strMessage As String
Dim ReadAll As String




strComputer = "ComputerNameHere"
strMessage = "Testing Net Send"




Set objExec = objShell.Exec("Net Send " & strComputer & " " & strMessage)




Do While Not objExec.StdOut.AtEndOfStream
ReadAll = objExec.StdOut.ReadAll
Loop




MsgBox ReadAll, vbInformation, "Net Send Results"


End Sub

I've found this code but it advises to "Within VBA, ensure you add a reference to "Windows Script Host Object
Model", then you may copy and paste the following code into a new Module:"

My question is how do you add a refrence to this object in vba?

Has anyone found a better way to net send through excel?

Thanks very much in advance,

Jon

Re: Ping a Computer

I've looked at the timing function in VBA, but i'm stuck, i'm looking for a loop that will run the ping every 60 seconds for 5mintues say.

Any Ideas?

Thanks very much,

Jon

Re: Ping a Computer

That is cool, very simple and quick.

It should not be two hard now to put that into a loop that repeats itself every 60 seconds say, and send an email if a false result is returned.

Only seems to work on Ipaddresses though and not Computer names.

Do you know of anyway to do a ip address lookup with a machine name in vba?

Thanks

Jon

Hi,

I am looking into writing a small VB script / macro that can ping a computer to check it is available. I've done a search on Ping in the forrum and people have mentioned doing ping through the 'shell' I am unsure what this and how it links in with Excel.

Ideally if the computer is down it will send out an email and when it comes back up.

Has anyone written anything similar?

Thanks in advance,

Jon

Re: Remove Repeated Values from a list

Thanks very much, i've got it working now.

Hi,

I am trying to create a script that when run produces a list of values from a source data list. However the source data list may contain the same value more than once, how do I get it to remove the repeated values and show it in this new list?

Thanks,

Jon

Re: File Not Found

Works a treat thanks very much!

Hi,

I ahve a piece of VBA code that checks the last modified date of file, and this is printed into a cell in a worksheet.

The problem i've hit is if the file is missing it produces an error message box, I can't figure out how to stop the error msg and just get "file not found!" printed in the cell instead.

Let Sheet1.Cells(lngRow, 2).Value = FileLastModified("\\server\testfile.txt")

Function FileLastModified(strFullFileName As String)
    Dim fs As Object, f As Object, s As String
     
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullFileName)
     
    's = UCase(strFullFileName) & vbCrLf
    s = s & f.DateLastModified
    
    If filenotfound Then
    
    FileLastModified = s
     
    Set fs = Nothing: Set f = Nothing


End Function

Thanks very much,

Jon

Hi,

I am trying to write a peice of VBA that can be attached to macro that will process a column of data in an excell sheet. I want the macro to check the number of characters in each cell if it is greater than 18 then to copy the whole row to a blank spreadsheet.

i.e Cell A1 has more than 18 characters therefore copy row A to sheet 2.
Cell B1 is less than 18 characters therefore do nothing go onto the next row.

Does this make sense?

Thanks for any help!

Jon

Re: Find the modified date of a file?

Hi,

I am using the code above to find the last modiefied date of file, but I am also trying to find a away that if the file was not found it would flag up an error message and do something else...etc

Any ideas?

Thanks

Jon

Re: Count worksheets

Thank you!