I am working on a project to download the full list of domains that my home network has been to in a day (or days) from OpenDNS. I have most of this working, but one piece keeps hounding me.
I can't activate the newly downloaded CSV file from OpenDNS to manipulate it. I can get it open, but even when I stop my code after it is open, I can't see it with VBA. I have tried (1) Looping through all of the open Workbooks, (2) Attempting to activate it and deal with any errors, and (3) Using Immediate window to say Workbooks("Its Name").Activate. VBA cannot see it open.
Are CSV files not considered Workbooks? Can I loop through text files??? Any other thoughts would be greatly appreciated.
I am embarrassed to post my code, because it is currently very sloppy, but... - Option Explicit
-
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
-
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
-
Dim strWkBk As String
-
Dim h As LongPtr
-
Dim ie As Object
-
Enum CheckingStatus
-
xKeepLooking = 1
-
xCanceled = 2
-
xFound = 3
-
End Enum
-
-
Public KeepLooking As CheckingStatus
-
Public FileToLookFor As String
-
Public SecondsPerLoop As Long
-
-
-
-
Sub DnldDNS()
-
'On Error GoTo Ooops
-
Application.DisplayAlerts = False
-
-
Dim i, InitI, z As Integer
-
z = 0
-
Dim my_url As String
-
Dim strPW As String
-
Set ie = CreateObject("InternetExplorer.Application")
-
my_url = "https://login.opendns.com/?return_to=http%3A%2F%2Fdashboard.opendns.com%2F&__utma=247635969.2077072011.1376532311.1383186116.1383695984.12&__utmb=247635969.1.10.1383695984&__utmc=247635969&__utmx=-&__utmz=247635969.1376532311.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)&__utmv=-&__utmk=83849365"
-
strPW = InputBox("Password?")
-
Dim strBegin, strEnd As String
-
strBegin = InputBox("What date would you like to start with?", "yyyy-mm-dd format")
-
strEnd = InputBox("What ending date would you like?", "If left blank, it will default to the starting date.")
-
If strEnd = "" Then strEnd = strBegin
-
-
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = strEnd
-
Sheets(strEnd).Range("A1:BO1").Value = Sheet1.Range("A1:BO1").Value
-
-
With ie
-
.Visible = True
-
.navigate my_url
-
-
Sleep (3000)
-
-
Do While .Busy And Not .readyState = READYSTATE_COMPLETE
-
DoEvents
-
Loop
-
Login:
-
'Note, the following email is an expired temp email
-
'from one of the temp email sites.
-
.document.getElementById("username").Value = "i1908056@trbvm.com"
-
Sleep (250) 'Had to add sleep to give it time to complete each of these tasks
-
-
.document.getElementById("password").Value = strPW
-
Sleep (250)
-
-
.document.getElementById("sign-in").Click
-
Sleep (3000)
-
-
Do While .Busy And Not .readyState = READYSTATE_COMPLETE
-
DoEvents
-
Loop
-
-
If Left(ie.LocationURL, 41) = "https://login.opendns.com/?return_to=http" Then
-
GoTo Login
-
End If
-
-
Do Until .readyState = READYSTATE_COMPLETE
-
Loop
-
-
.navigate "https://dashboard.opendns.com/stats/all/topdomains/today/"
-
-
Do Until .readyState = READYSTATE_COMPLETE
-
Loop
-
-
.navigate "https://dashboard.opendns.com/stats/all/topdomains/2015-11-29.csv"
-
-
Do Until .readyState = READYSTATE_COMPLETE
-
Loop
-
-
Sleep (1000)
-
-
Call OpenSaveCancel
-
-
Sleep (5000)
-
-
'********************************************************************************************
-
'****I need to activate the just opened workbook here, or even better loop until it is open
-
'********************************************************************************************
-
-
Workbooks(strWkBk).ActiveSheet.Range("A2:BO" & ActiveSheet.UsedRange.Rows.Count).Select
-
-
Selection.Copy
-
-
Workbooks("OpenDNS Work.xlsm").Activate
-
ActiveSheet.Range("A2").Select
-
ActiveSheet.Paste
-
-
Workbooks(strWkBk).Close
-
-
End With
-
-
ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 2).Select
-
-
For i = 2 To 25
-
With ie
-
.navigate "https://dashboard.opendns.com/stats/all/topdomains/2015-11-29/page" & i & ".csv"
-
-
Sleep (1000)
-
-
Call OpenSaveCancel
-
-
Sleep (5000)
-
-
'********************************************************************************************
-
'****I need to activate the just opened workbook here, or even better loop until it is open
-
'********************************************************************************************
-
-
If Workbooks(strWkBk).ActiveSheet.Range("A2") = "" Then GoTo ExitSub
-
-
Workbooks(strWkBk).Activate
-
-
If Workbooks(strWkBk).ActiveSheet.Range("A2") = 1 Then
-
GoTo ExitSub
-
End If
-
-
Workbooks(strWkBk).ActiveSheet.Range("A2:BO" & ActiveSheet.UsedRange.Rows.Count).Select
-
-
Selection.Copy
-
-
Workbooks("OpenDNS Work.xlsm").Activate
-
-
ActiveSheet.Paste
-
-
ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 2).Select
-
-
Workbooks(strWkBk).Close
-
End With
-
Next i
-
-
ExitSub:
-
Application.DisplayAlerts = True
-
-
Workbooks(strWkBk).Close
-
-
ie.Close
-
-
MsgBox ("Done")
-
-
Exit Sub
-
-
Ooops:
-
-
-
End Sub
-
-
-
Function OpenSaveCancel()
-
Dim o As IUIAutomation
-
Dim e As IUIAutomationElement
-
Set o = New CUIAutomation
-
h = ie.hwnd
-
h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
-
If h = 0 Then Exit Function
-
-
Set e = o.ElementFromHandle(ByVal h)
-
Dim iCnd As IUIAutomationCondition
-
Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Open")
-
-
Dim Button As IUIAutomationElement
-
Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
-
Dim InvokePattern As IUIAutomationInvokePattern
-
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
-
InvokePattern.Invoke
-
End Function
-
-
11 3563
Here is an addition. VBA can definitely see CSV files (which I should have known). BUT... It can not see the newly downloaded CSV while my code is in process. If I stop the code, and then run a loop... There it is.
Why would it not be able to see that CSV?
I found a work around, and the work around is actually a bit faster. I still wish I knew why the CSV was not accessible, but...
I ended up saving the CSV to my download folder, and then open it. I then of course had complete control of it.
zmbd 5,501
Expert Mod 4TB
I think your terminonlogy is "throwing" people.
A CSV file is not something one "activates" - it's a static. Q: Are CSV files not considered Workbooks? A: - NO Q: Can I loop through text files??? A: - Yes: home > topics > microsoft access / vba > insights > vba standard text file i/o statements On a local drive, open the file for input, depending on what you want, either as random or simply as a forward-only "record set" -- Keep in mind, the CSV is not a database nor a workbook. I've also never tried the standard IO statements on a hyperlinked document, I suspect that these will fail in such a usage.
+ +Although they (CSV) can be opened within Excel, the CSV is a comma delimited text file. Excel simply has the gumption to place each "element" in its own cell until it hits the end-of-line or some other marker that indicates the end of the element group; thus starting on a new row within the worksheet. You can just as easily open the CSV in Word, Notepad, Powershell, or any program capable of opening a plan ASCII text file.
+ + IN post 1 you say that you've downloaded the file; however, in post 3 you say that your workaround is to download the file. This doesn't make any sense.
+ + Nowhere in your code block do you actually open the document in excel... You browse to the document on-line; however, you never actually open it in anyway that I can see that excel would recognize... nor does any of your code appear to down-load the file to the local drive.
You need something like (this is off the top of my head, I haven't tried this myself - just a starting point): - '...other code....
-
Dim zHyperLink as string
-
zHyperLink = "https://dashboard.opendns.com/stats/all/topdomains/2015-11-29.csv"
-
Dim zWindow = "2015-11-29.csv"
-
workbooks.open Filename:= zHyperLink
-
Window(2015-11-29.csv).Activate
-
'code to copy cells to where you need
-
'etc...
+ + We do appreciate that you posted your code... it shows that you are willing to make an effort and that goes a long ways towards encouraging others to help. However, what you are doing I suspect isn't too common (although not unheard of); thus, I suspect the main reason behind the slow response.
>>> Please note, although I missed it initially, we do ask that users not use their real emails, server names, user names etc... in the posted code, I've changed your's in the code to one of the disposable emails - just a reminder to those that follow, Please make sure and scrub your code and posts of sensitive information unless you like the spam-bots };-)
Thanks for the info. I love the article you wrote on IO statements. I have not used these enough to be proficient, so I saved your article for the next time that I do.
I was aware that CSV docs were not workbooks, but Excel has the ability to deal with them (as you mentioned) as if they were (when they are opened in Excel). Hence my 2nd post. I opened a local CSV, and then was able to see it when I looped through opened workbooks with VBA.
My apologies on the confusion with downloading in post 1 and 3. What I meant to say on post 3, that instead of opening the file from the download, I saved the file instead, and then, opened it local.
The opening (and in this case saving) happened in Function OpenSaveCancel(). I'm not sure who initially posted this code, but it sure is handy. I had it set to Open, but to make it save, I just had to change this line... - Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save")
I thought of activating the containing window, but wasn't sure how to do it. I just tried your suggestion of Windows("2015-11-29.csv").Activate, and no luck. It didn't error out, but it also did not activate it. However, even if I did get the window activated, I still would have to latch on to the Excel contained inside.
And finally, sorry about the email. I did scrub my code prior to posting, but unfortunately, missed that. Thanks for cleaning it up for me.
Thanks again for the insight.
zmbd 5,501
Expert Mod 4TB
It was worth a shot. Windows("2015-11-29.csv")
Look at the title in the window of the "opened" CSV that's more than likely what we'll have to go for... otherwise we'll have to loop thru either the opened windows or open workbooks.
+ Open a new workbook, vbe, and put this code either in the "ThisWorkbook" object or a new module.
+ Open a few other workbooks on your drive, for best effect, arrange in cascade. Select one of the workbooks in the back of the stack (Don't double click, simply click on the titlebar :) )
+ Now from the open VBE, click into the code, [F5]
+ As the loop progresses you get a bit of visual and user feedback. The msgbox is only there to pause the code otherwise it moves so fast one could not see the effect.
+ If your VBE is covering the cascaded workbooks, the effect is a bit more noticeable. :) I highly recommend dual monitors if your PC will handle it, and extend the desktop - I'll never willing go back to a single monitor while developing.... even in the lab I usually have a database/workbook open in one and my email, IE, etc open on the other. I keep telling IT they should buy me a 56" monitor :ROTFL:
+ I leave the actual practical application to you :) - Option Explicit '<< Check for duplicate, delete one if found
-
'
-
Sub loopopenbooks()
-
Dim zwrkbooks As Excel.Workbooks
-
Dim zwrkbook As Excel.Workbook
-
Dim zstr As String
-
Dim z911 As Boolean
-
'
-
On Error GoTo zerrtrap
-
z911 = False
-
'
-
ThisWorkbook.Activate
-
Set zwrkbooks = Application.Workbooks
-
For Each zwrkbook In zwrkbooks
-
MsgBox prompt:="Loop Workbook := " & zwrkbook.Name & vbCrLf & "Actvie Workbook := " & ActiveWorkbook.Name
-
zwrkbook.Activate
-
MsgBox prompt:="Loop Workbook := " & zwrkbook.Name & vbCrLf & "Actvie Workbook := " & ActiveWorkbook.Name
-
Next zwrkbook
-
'
-
zcleanup:
-
If Not zwrkbooks Is Nothing Then Set zwrkbooks = Nothing
-
Exit Sub
-
zerrtrap:
-
zstr = "ErrS: " & Err.Source & vbCrLf & _
-
"Errn: " & Err.Number & vbCrLf & _
-
"ErrD: " & Err.Description & vbCrLf
-
If z911 Then
-
MsgBox prompt:=zstr, _
-
Buttons:=vbCritical + vbOKOnly, _
-
Title:="Oh Bother, Fatal error - no recovery"
-
Exit Sub
-
Else
-
MsgBox prompt:=zstr, _
-
Title:="Oh Bother, Something went wrong again"
-
End If
-
z911 = True
-
Resume zcleanup
-
End Sub
I have not used these enough to be proficient, so I saved your article for the next time that I do.
This used to be the old-school database (Think DOS3.1). When I was a teenager, used these methods extensively to design databases. A friend and I even had a small, simple, circulating library developed using this method and file open for input as random etc... the lines of code, don't know if I have the strength left in the eyes to recreate that!
This is doing what I was attempting in activating the newly downloaded CSV, but VBA just couldn't see it. It works great when the files are local files, but not so when they are files that were opened directly from download.
I LOVE your error handler. Winnie the Pooh fan are you? :)
zmbd 5,501
Expert Mod 4TB ....The wonderful thing about Tiggers
Is Tiggers are wonderful things!
Their tops are made out of rubber
Their bottoms are made out of springs!
They're bouncy, trouncy, flouncy, pouncy
Fun, fun, fun, fun, fun!
But the most wonderful thing about Tiggers is
I'm the only one ....
It's not that VBA cannot see the file, it is that Excel isn't seeing the file. You still need something like line5 in post#4 to actually open the file in Excel, and your Function OpenSaveCancel(). doesn't have that in the code posted in post#1 - you may have the file open in the browser; however, it's not going to be easily accesable to Excel without going into the Document Module of the browser, depending on what browser you are using. (I have a few posts covering DocMod for IE here... been awhile back (we'll have to hunt for them) and he information is not directly applicable but nonetheless related.)
The thing is, if the file is open in Excel, then the application has to be able to see it; hence, my use of the Application.workbooks object....
The only other option is to attempt the window loop
Modification of the afore posted... once again, however, I am calling the application object in the following code... - Sub loopopenwindows()
-
Dim zwindows As Windows
-
Dim zwindow As Window
-
Dim zstr As String
-
Dim z911 As Boolean
-
'
-
On Error GoTo zerrtrap
-
z911 = False
-
'
-
ThisWorkbook.Activate
-
Set zwindows = Application.Windows
-
For Each zwindow In zwindows
-
MsgBox prompt:="Loop Workbook := " & zwindow.Caption & vbCrLf & "Actvie Workbook := " & ActiveWindow.Caption
-
zwindow.Activate
-
MsgBox prompt:="Loop Workbook := " & zwindow.Caption & vbCrLf & "Actvie Workbook := " & ActiveWindow.Caption
-
Next zwindow
-
'
-
zcleanup:
-
If Not zwindows Is Nothing Then Set zwindows = Nothing
-
Exit Sub
-
zerrtrap:
-
zstr = "ErrS: " & Err.Source & vbCrLf & _
-
"Errn: " & Err.Number & vbCrLf & _
-
"ErrD: " & Err.Description & vbCrLf
-
If z911 Then
-
MsgBox prompt:=zstr, _
-
Buttons:=vbCritical + vbOKOnly, _
-
Title:="Oh Bother, Fatal error - no recovery"
-
Exit Sub
-
Else
-
MsgBox prompt:=zstr, _
-
Title:="Oh Bother, Something went wrong again"
-
End If
-
z911 = True
-
Resume zcleanup
-
End Sub
Unfortunatley Line5 in Post 4 -- No workie. OpenDNS has it locked down so that you can't do that. Not sure how, but I get a random OpenDNS security page when I go that route.
However, your code this time worked! What I had not tried was saying Application.Windows. That was the trick. It made it only look through Excel windows. Beautiful!
Finally... I'm one step closer to proficient. :)
Wait a minute...
Typically Application. is understood when you just reference "Workbooks()". With that being the case, I'm lost again. Sigh.
I really had tried looking through all open Workbooks, and the only one that was seen was the one that the code was written in.
I need to go home. I'm losing it. It's application.windows, not application.workbooks. I'm going to quit talking now before I display additional idiocy.
zmbd 5,501
Expert Mod 4TB
BHo15: Go home. Take a Nap. Eat Something... many a time I've been having a black hole moment and taking a break allows things to escape the event horizon.
:)
It does sound like we're in the right track.
Let us know how the project goes after lunch!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ole Kramer, SFS GmbH |
last post by:
We have installed PHP 5.0.1 on a Windows 2003 Server with IIS6 in ISAPI
mode. The processing of .php files works fine with our basis version of the
php.ini file. But, as soon as we edit the php.ini...
|
by: mrjolly |
last post by:
I have VB6 code that creates Excel v8 workbooks/worksheet/controls/macros
based
upon a text file.
I have created a button on a worksheet that activates a piece of code.
Unfortunately, the...
|
by: Maria Bitsku |
last post by:
How do I deactivate a window using Javascript. For example if I have
a window that opens up another window, how do I prevent the user from
clicking (doing anything) in the original window until...
|
by: Jeppe 1971 |
last post by:
Hi
Does anyone know of a way to load a HTML-page and examine the document
without activating the code?
I am trying to create a search-function in javascript.
The idea is that the function...
|
by: magmike |
last post by:
When users of my site submit articles to our database
(http://www.netterweb.com/articles/) they almost always have http://
addresses within the text. They are pasting that text into a box which...
|
by: Patrick_Montana |
last post by:
I am having a problem auto-activating a macro when I change data in a
field on a form that is a combo box. I have a fair understanding of
Microsoft Access but am not a programmer so I am trying to...
|
by: Jon Barron |
last post by:
I've (finally!) gotten the Application Updater block to work (only took eight hours - thanks to Zac Hamilton's walkthough @...
|
by: Robert Rotstein |
last post by:
I want to write a SOAP extension that gets activated from both the
client and the server. From whast I have read, this should be quite
easy to do. Yet I can only activate it from the server.
I...
|
by: Robert Rotstein |
last post by:
I want to write a SOAP extension that gets activated from both the
client and the server. But so far, I have only been able to activate
from the server.
I have a service (.asmx file) which...
|
by: Hrvoje Vrbanc |
last post by:
Hello all!
As I have only recently started to use native ASP.NET 2.0 data access
controls (and found them to be very powerful), I have one question that I
was unable to find the answer to:
-...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |