JP2R
06-16-2008, 12:22 PM
Greetings All -
I had previously asked for help in setting up a worksheet - and have been trying to make the changes on my own - to much avail...
I'm new to VBA and am trying to teach myself by trail and error (mostly error)
Here is my situation:
I have a worksheet that I'm updating from another worksheet. The original of which I currently have to cut and paste from IE.
I had asked about this previously but found that sites that were HTTPS vice HTTP (as in secure) it is not possible to import the table. My work around had been to right click on the webpage or frame - select print - and print it off using the Mircosoft XPS printer.
I have tried to figure out how to automate the process on my own, but do not know how to call another program to open much less as it to copy and paste the screen...
So...
I go into Explore and find the file - right click on the file - open with IE
Once the page is open - I select all - and paste into Excel 2007 - where I am given the chose to paste as is - or to import a table (which I choose to do) -
I get the following columns:
Mac Address 1 /// Mac Address 2 /// SSID /// Number of connections /// Alerts
The problem is that the SSID is delimited and splits up into several columns.
Initially I had set up a function in a cell to concatenate the columns into one so that I could copy and paste the value for the SSID into one column.
Which means that I have to go back and remove the "number of connections" and the "alerts".
I was looking to automate this...
Here is my feeble attempt:
Function FindStuff()
Dim x As UniqueValues
Dim LastCell As Range
For Each x In Range("D2:G17")
If x.Row > LastCell Then: Exit For
If IsEmpty(x.Value) Then: x.Value = " "
If IsNumber(x.Value) Then: x.Value = " "
If (x.Value) = "Alert" Then: x.Value = " "
Next x
End Function
I'm not sure if this should be in a module/or sheet or/ worksheet
Whether it should be a function or a sub...
Your direction and assistance would be very much appreciated.
Sincerely
Jae
I am using Office Professional 2007
Dell LapTop
I had previously asked for help in setting up a worksheet - and have been trying to make the changes on my own - to much avail...
I'm new to VBA and am trying to teach myself by trail and error (mostly error)
Here is my situation:
I have a worksheet that I'm updating from another worksheet. The original of which I currently have to cut and paste from IE.
I had asked about this previously but found that sites that were HTTPS vice HTTP (as in secure) it is not possible to import the table. My work around had been to right click on the webpage or frame - select print - and print it off using the Mircosoft XPS printer.
I have tried to figure out how to automate the process on my own, but do not know how to call another program to open much less as it to copy and paste the screen...
So...
I go into Explore and find the file - right click on the file - open with IE
Once the page is open - I select all - and paste into Excel 2007 - where I am given the chose to paste as is - or to import a table (which I choose to do) -
I get the following columns:
Mac Address 1 /// Mac Address 2 /// SSID /// Number of connections /// Alerts
The problem is that the SSID is delimited and splits up into several columns.
Initially I had set up a function in a cell to concatenate the columns into one so that I could copy and paste the value for the SSID into one column.
Which means that I have to go back and remove the "number of connections" and the "alerts".
I was looking to automate this...
Here is my feeble attempt:
Function FindStuff()
Dim x As UniqueValues
Dim LastCell As Range
For Each x In Range("D2:G17")
If x.Row > LastCell Then: Exit For
If IsEmpty(x.Value) Then: x.Value = " "
If IsNumber(x.Value) Then: x.Value = " "
If (x.Value) = "Alert" Then: x.Value = " "
Next x
End Function
I'm not sure if this should be in a module/or sheet or/ worksheet
Whether it should be a function or a sub...
Your direction and assistance would be very much appreciated.
Sincerely
Jae
I am using Office Professional 2007
Dell LapTop