Posts by charlesa920

I have a local area network with a couple of hundred computers which share one internet connection. Each user has a CPE (customer premise equipment) with an assigned (by me) IP. This CPE connets to the users equipment via CAT5. My responsibility is to provide internet. Sometimes users call and say their comupter cannot access the internet. I need a quick way to see if the problem lies with my network or if the problem is with the customer's computer.

It seems to me that if I could open my spreedsheet with all the network connections and user data and simply ping their IP I would see if the problem is with my equipment or the users.

Column A of my spreedsheet has the actual IP addresses: 192.168.1.1 thru 192.168.1.254 and 10.0.0.1 thru 10.0.0.254 but not all are currently being used. Each row has distinct user account information.

I have created a shortcut, named it PING106.bat and listed the target as %windir%\system32\ping.exe 192.168.1.106 which I can click on and it runs. Next I inserted a hyperlink in A:107 and it does work (it brings up a DOS screen and pings 192.168.1.106 three times then closes the DOS screen)... But there must be a better way. I don't want to create hundreds of shortcuts and insert hyperlinks to specific cells one at a time.

It would be nice if I could click on a cell which contains an IP and know if that particular IP is up and reachable on my LAN.

Thanks for any ideas,
Charles

BTW as a heads up. If this PINGING can be done the next step is to output something which indicates if the link is up or down. the next step is to conditionally formatt the cell green/red. and to automate the process to run every couple of minutes.... but that is the stuff for another thread...

Re: Change Hex Number To Dec Number

I had a similar problem of needing to convert hex2dec or dec2hex. application had to determine which coversion was needed on a case by case basis. It is part of an old inventory receiving program for Cell phone. Cell phones used to have HEX & DEC Electronic Serial Numbers. Now they are using IMEI so I don't believe this bit of code what you want but may give some insite...

the HEXESN is always 8 digits
the DECESN is always 11 digits
the first 2 HEX digits equate to the first 3 digits in the DEC

Function ConvertESN(strESN As String, strHex As String, strDec As String) As Boolean
    
    ConvertESN = False
    
    Select Case Len(strESN)
    
        Case Is = 11
            If IsNumeric(strESN) Then
                strHex = HEX(Left$(strESN, 3)) & HEX(Right$(strESN, 8))
                strDec = strESN
                ConvertESN = True
                If Len(strHex) > 8 Then ConvertESN = False
            End If
            
        Case Is = 8
            If IsValidHex(strESN) Then
                strDec = GetDecimal(strESN)
                strHex = strESN
                ConvertESN = True
            End If
        
    
    End Select
    
End Function


Function IsValidHex(strESN As String) As Boolean
    
    Dim intPos As Integer
    Dim strValidHexValues As String
    strValidHexValues = "0123456789ABCDEF"
    
    IsValidHex = True
    
    For intPos = 1 To Len(strESN)
        If InStr(strValidHexValues, Mid$(strESN, intPos, 1)) = 0 Then
            IsValidHex = False
            Exit Function
        End If
    Next
    
End Function




Function GetDecimal(strESN As String) As String


    Dim lngDecESN As Long
    Dim strHexValues As String
    strHexValues = "123456789ABCDEF"


' First two bytes
    lngDecESN = 16 * InStr(1, strHexValues, Mid$(strESN, 1, 1))
    lngDecESN = lngDecESN + InStr(1, strHexValues, Mid$(strESN, 2, 1))
    GetDecimal = lngDecESN


' Next six bytes
    lngDecESN = 1048576 * InStr(1, strHexValues, Mid$(strESN, 3, 1))
    lngDecESN = lngDecESN + 65536 * InStr(1, strHexValues, Mid$(strESN, 4, 1))
    lngDecESN = lngDecESN + 4096 * InStr(1, strHexValues, Mid$(strESN, 5, 1))
    lngDecESN = lngDecESN + 256 * InStr(1, strHexValues, Mid$(strESN, 6, 1))
    lngDecESN = lngDecESN + 16 * InStr(1, strHexValues, Mid$(strESN, 7, 1))
    lngDecESN = lngDecESN + InStr(1, strHexValues, Mid$(strESN, 8, 1))
' The above code will drop a leading zero in the "next six bytes".
' this next line of code was added to
' Handle 7-byte decimals - this is the only line I added ...
    If Left(Str(lngDecESN), 1) = " " Then GetDecimal = GetDecimal & "0"


    GetDecimal = GetDecimal & lngDecESN


End Function

Re: Concanetate Or User Input, And Filldown

Thanks! That works like a charm.
AND I was able to examine the code and get an understanding of HOW it does it.

Thank you again,
Charles

My cellular carrier supplies my monthly commission reports where column "S" indicates the month and column "T" indicates the year. The first few hundred rows will indicate the month like "NOV" and the reminding rows will have it like "11".

The year will always be yyyy.

I want to combine these into one column where it will read like "NOV, 2006" so it will match other months already entered.

Each month has its own worksheet.

I started to concanetate and fill down all the active rows.
I found this snippit of code but cannot get it to work:

Range("U1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"", ""&RC[-1]"
ActiveCell.Range("U1:U" & LastMSRow).FillDown

But I got to thinking,..Should I open an InPutBox? or something and have the user just type in what they want. This would solve the "NOV" and "11" issue. But I'm still left with xlFillDown? and I don't know how to get it to stop when it finds an empty row.

Hope I'm not breaking forum rules by asking 2 questions?
1) Should I use the concanetate & fill down or require user input?
2) How do I get the auto fill down to work as needed?

Thanks,
Charles

Thanks, Charles

Re: Pre-fixing Numbered Rows With Cell Information

brianNC

Looks like you are doing a science fair project, hope you do well, take First Place, finish high in regionals, and get a scholarship! For the record, more PSI will make the ball bounce higher.

I'm probaly wasting everyone's time as the suggestions above are viable, and more elegant than my techinque...

I would use a hidden column, or a column on another sheet. Put numbers in the column, and ref to there. this will get the number. It doesn't even have to be a hidden sheet as you can set the print area before you print your data tables

if your hidden column is "X" you could put this formula in row 2 and drag it down.

And instead of typing the X2 in the formula bar you could just click on it.

=$A$1&" "&X2

Re: Formula

What are you going to do with the results?
Keep it as a perm record? Print it? Just look it up on demand?
this could make a difference as to the approach...

If all you want to do is look at it on demand, one record at a time...
If you need to print it for each customer, each month...
If you intend to create a perm record...but if you already have the perm record there would be no need to store the same information twice when you can always mine the data for your results.

Re: Parse A Delimited String

If the value in the cell is the result of a formula, that is you can see the results in the cell but you see a formula in the formula bar...

You can do a copy and the paste it back in the same cells using "paste special" =values

Now the formula has been replaced by its returned value.

Re: Paste Column But Change Order

Okay Great!

That works wonders in the contiguous columns

I had no idea you could do that!!!

Thanks
Charles

Re: Write This Formula

I believe tomach is on the right track but has inadvertantly left out one set of brackets...

The order of math opperations is * / before + -

so =1000*A1+0.256 will multipy 1000*A1 and then add .256

where 1000*(a1+.256) is what you are looking for

=(((1000*(A1+0.256))-(9*1.0625))/(1000*1.0625))-0.314

I have two worksheets... "TRANSACTIONS" and "MANUAL ADJUSTMENTS"
The two sheets have similar types of data but the columns are in different orders.

Here is what I've got so far:

Sub MoveColumnsFromMANUALtoTRANSACTIONS()
'
' MoveColumnsFromMANUALtoTRANSACTIONS Macro
' get data on sheet "Manual Adjustments" and past to proper columns on "Transactions"
'


'
    Sheets("MANUAL ADJUSTMENTS").Select
    Range("A1").Select
Dim myColumn As Range
Set myColumn = Range(Range("A2"), Range("a2").End(xlDown).Offset(0, 0))
    myColumn.Copy
    
    Sheets("TRANSACTIONS").Select
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
' that seemed to work
' but now I see why some go to the bottom and xlUp    
'  now for column B
    Sheets("MANUAL ADJUSTMENTS").Activate
Set myColumn = Range(Range("b2"), Range("b2").End(xlDown).Offset(0, 0))
    myColumn.Copy
    
    Sheets("TRANSACTIONS").Select
    Range("b2").Select
    Selection.End(xlDown).Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
' Ok that worked for column B
' now I need to copy and paste columns: C-C, D-D, E-E, F-F, G-k, (don't use H&I)
'  J-G, K-Q, L-R, N-S, O-T
' It would be nice to have a more elegant way than just rewrite same code 10 more times
End Sub

I am happy to search for solutions (I've learned tons just reading other posts) but I don't seem to know what to search for...

any help is very much appreciated!

Re: Copy 2 Sheets And Close, variable Wbk Names

WE got it working!:p I say "WE" because I could not have done it alone...Thank you so much!

right after the dialogbox I inserted this line:
NewWrkbkName = ActiveWorkbook.Name

then after I copied the sheets I inserted this line:
Workbooks(NewWrkbkName).Activate

looks like this now:

Sub CopySheetsToHereFromCurrentdownloadxls()
'
' CopySheetsToHereFromCurrentdownloadxls Macro
' Macro recorded 1/27/2007 by charles
'
    Dim MyWrkbkName As String
    Dim NewWrkbkName As String
     
    MyWrkbkName = ThisWorkbook.Name
    
'   open a specific file
'
    Application.Dialogs(xlDialogFindFile).Show
'   assigning variable name to new workbook
    NewWrkbkName = ActiveWorkbook.Name


'   lets try an Array to copy both sheets at one time
    Sheets(Array("MANUAL ADJUSTMENTS", "TRANSACTIONS")).Copy Before:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets(1)
'   Now we need to activate the workbook with this month's data
     Workbooks(NewWrkbkName).Activate
     ' next step is to close the NewWrkbkName and save changes = false
     ' checking to make sure that a new workbook has been opened
     ' and not been canceled by the user by comparing two variables
     ' so that we don't close the instance of Excel with the macro


    If NewWrkbkName <> MyWrkbkName Then
         ' turning off warning alerts
        Application.DisplayAlerts = False
         'Closing the active workbook
        Workbooks(NewWrkbkName).Close
         ' turning on warning alerts
        Application.DisplayAlerts = True
    End If


End Sub

Re: Copy 2 Sheets And Close, variable Wbk Names

Yes, thank you.

This is going to work, it doesn't yet, but I'm sure it will.

After it copies the sheets to:
MyWrkbkName, MyWrkbkName is active and it does not close either

I think if we could make NewWrkbkName active, we could close it (and not save changes)

Could you suggest a line of code to activate the NewWrkbkName?

Thanks to the help from this Forum and its members: I can now open a file and copy two sheets but need to close the second file...

In wkb "C*commissions database.xls" I have this code which directs the user to open the current month's commissions. And with it open I can copy two sheets (always named the same). but now I need to close the current month's workbook, so I can continue working with the data in my permenant workbook.

Sub CopySheetsToHereFromCurrentdownloadxls()
'
' MoveSheetsToHereFromCurrentdownloadxls Macro
' Macro recorded 1/27/2007 by Charles
'


'   open the download for this month
'
    Application.Dialogs(xlDialogFindFile).Show


 '  lets try an Array to copy both sheets at one time
    Sheets(Array("MANUAL ADJUSTMENTS", "TRANSACTIONS")).Copy Before:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets(1)  
'  okay I've got the new sheets in my database workbook
'  now I need to close the download for this month
'  but the name of the file changes each month


 End Sub

Thank you for taking the time to read this, hope you can give me a suggestion. Charles

Re: Import Access into Excel has bad text format

I had a similar problem with dates. The cells in Excell looked like dates but were not because when I chagned the format of the cells to a different date format, the cells not change to the new format.

But if I select the cell and click in it, the format will change when the cell lost focus.

I ran the code above and sure enough, the correct format showed up.
Then just to be sure I pasted the same dates in 5 columns, formated them all different, ran the trimmer(), and they all changed to the correct format

Re: Have User Browse For File To Copy Sheets

Hey! Thanks!
That was too easy.
I did a search but instead of searching for "inputbox" this time I searched for "getopenfilename" and here is what I found...

Sub getopenfilename()
    Application.Dialogs(xlDialogFindFile).Show
    
End Sub

If you could see me now, you would see me smiling ear to ear.:p

One of my wireless carriers provides commission data in Excel, each of their downloads has a specific name which refers a month’s commissions. I’ve thought about making a copy of each month’s Workbook when it is available with the name “current download.xls” and then work with it in a know directory.

I’ve just recently discovered InputBox and am wondering if I can use it to ask the user to find (Browse for) the actual file they want, thus saving the step of creating and renaming a copy of the actual file.

Since I like to work with the files as does another Director of my company, and the actual workings are handled on another management level I do not want to point to a specific directory.

I’m thinking there is a way to copy the needed sheets without opening the file but I can save that for another question. First things first...thanks

Here is some code I recorded then tweaked a little, sorry if it isn’t elegant…

value
Sub MoveSheetsToHereFromCurrentdownloadxls()
'
'     MoveSheetsToHereFromCurrentdownloadxls Macro
'     Macro recorded by charles 
'
'
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Charles\Desktop\FOLDERS\H*Heartland\commissions & smf download\currentdownload.xls"


'
    Windows("currentdownload.xls").Activate
 '  add Manual adjustments as sheet #1 in C* Commission database.xls
 '  and then add Transactions just after Manual Adjustments
 
    Sheets("MANUAL ADJUSTMENTS").Move After:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets(1)


     Windows("currentdownload.xls").Activate
    Sheets("TRANSACTIONS").Move After:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets("MANUAL ADJUSTMENTS")
    Windows("currentdownload.xls").Activate
    ActiveWorkbook.Close SaveChanges:=False
    ActiveWindow.WindowState = xlMaximized
    Range("B9").Select
End Sub

thanks for any suggestions