473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Need help activating a newly downloaded CSV

BHo15
143 128KB
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...

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  3. 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
  4. Dim strWkBk As String
  5. Dim h As LongPtr
  6. Dim ie As Object
  7. Enum CheckingStatus
  8.     xKeepLooking = 1
  9.     xCanceled = 2
  10.     xFound = 3
  11. End Enum
  12.  
  13. Public KeepLooking As CheckingStatus
  14. Public FileToLookFor As String
  15. Public SecondsPerLoop As Long
  16.  
  17.  
  18.  
  19. Sub DnldDNS()
  20. 'On Error GoTo Ooops
  21.     Application.DisplayAlerts = False
  22.  
  23.     Dim i, InitI, z As Integer
  24.         z = 0
  25.     Dim my_url As String
  26.     Dim strPW As String
  27.         Set ie = CreateObject("InternetExplorer.Application")
  28.         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"
  29.         strPW = InputBox("Password?")
  30.     Dim strBegin, strEnd As String
  31.         strBegin = InputBox("What date would you like to start with?", "yyyy-mm-dd format")
  32.         strEnd = InputBox("What ending date would you like?", "If left blank, it will default to the starting date.")
  33.             If strEnd = "" Then strEnd = strBegin
  34.  
  35.     Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = strEnd
  36.     Sheets(strEnd).Range("A1:BO1").Value = Sheet1.Range("A1:BO1").Value
  37.  
  38.     With ie
  39.         .Visible = True
  40.         .navigate my_url
  41.  
  42.         Sleep (3000)
  43.  
  44.         Do While .Busy And Not .readyState = READYSTATE_COMPLETE
  45.             DoEvents
  46.         Loop
  47. Login:
  48. 'Note, the following email is an expired temp email
  49. 'from one of the temp email sites.
  50.         .document.getElementById("username").Value = "i1908056@trbvm.com"
  51.         Sleep (250) 'Had to add sleep to give it time to complete each of these tasks
  52.  
  53.         .document.getElementById("password").Value = strPW
  54.         Sleep (250)
  55.  
  56.         .document.getElementById("sign-in").Click
  57.         Sleep (3000)
  58.  
  59.         Do While .Busy And Not .readyState = READYSTATE_COMPLETE
  60.             DoEvents
  61.         Loop
  62.  
  63.         If Left(ie.LocationURL, 41) = "https://login.opendns.com/?return_to=http" Then
  64.             GoTo Login
  65.         End If
  66.  
  67.         Do Until .readyState = READYSTATE_COMPLETE
  68.         Loop
  69.  
  70.         .navigate "https://dashboard.opendns.com/stats/all/topdomains/today/"
  71.  
  72.         Do Until .readyState = READYSTATE_COMPLETE
  73.         Loop
  74.  
  75.         .navigate "https://dashboard.opendns.com/stats/all/topdomains/2015-11-29.csv"
  76.  
  77.         Do Until .readyState = READYSTATE_COMPLETE
  78.         Loop
  79.  
  80.         Sleep (1000)
  81.  
  82.         Call OpenSaveCancel
  83.  
  84.         Sleep (5000)
  85.  
  86.         '********************************************************************************************
  87.         '****I need to activate the just opened workbook here, or even better loop until it is open
  88.         '********************************************************************************************
  89.  
  90.         Workbooks(strWkBk).ActiveSheet.Range("A2:BO" & ActiveSheet.UsedRange.Rows.Count).Select
  91.  
  92.         Selection.Copy
  93.  
  94.         Workbooks("OpenDNS Work.xlsm").Activate
  95.         ActiveSheet.Range("A2").Select
  96.         ActiveSheet.Paste
  97.  
  98.         Workbooks(strWkBk).Close
  99.  
  100.     End With
  101.  
  102.     ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 2).Select
  103.  
  104.     For i = 2 To 25
  105.         With ie
  106.             .navigate "https://dashboard.opendns.com/stats/all/topdomains/2015-11-29/page" & i & ".csv"
  107.  
  108.             Sleep (1000)
  109.  
  110.             Call OpenSaveCancel
  111.  
  112.             Sleep (5000)
  113.  
  114.             '********************************************************************************************
  115.             '****I need to activate the just opened workbook here, or even better loop until it is open
  116.             '********************************************************************************************
  117.  
  118.             If Workbooks(strWkBk).ActiveSheet.Range("A2") = "" Then GoTo ExitSub
  119.  
  120.             Workbooks(strWkBk).Activate
  121.  
  122.             If Workbooks(strWkBk).ActiveSheet.Range("A2") = 1 Then
  123.                 GoTo ExitSub
  124.             End If
  125.  
  126.             Workbooks(strWkBk).ActiveSheet.Range("A2:BO" & ActiveSheet.UsedRange.Rows.Count).Select
  127.  
  128.             Selection.Copy
  129.  
  130.             Workbooks("OpenDNS Work.xlsm").Activate
  131.  
  132.             ActiveSheet.Paste
  133.  
  134.             ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 2).Select
  135.  
  136.             Workbooks(strWkBk).Close
  137.         End With
  138.     Next i
  139.  
  140. ExitSub:
  141.     Application.DisplayAlerts = True
  142.  
  143.     Workbooks(strWkBk).Close
  144.  
  145.     ie.Close
  146.  
  147.     MsgBox ("Done")
  148.  
  149.     Exit Sub
  150.  
  151. Ooops:
  152.  
  153.  
  154. End Sub
  155.  
  156.  
  157. Function OpenSaveCancel()
  158.     Dim o As IUIAutomation
  159.     Dim e As IUIAutomationElement
  160.     Set o = New CUIAutomation
  161.     h = ie.hwnd
  162.     h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
  163.     If h = 0 Then Exit Function
  164.  
  165.     Set e = o.ElementFromHandle(ByVal h)
  166.     Dim iCnd As IUIAutomationCondition
  167.     Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Open")
  168.  
  169.     Dim Button As IUIAutomationElement
  170.     Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
  171.     Dim InvokePattern As IUIAutomationInvokePattern
  172.     Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
  173.     InvokePattern.Invoke
  174. End Function
  175.  
  176.  
Dec 3 '15 #1
11 3563
BHo15
143 128KB
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?
Dec 3 '15 #2
BHo15
143 128KB
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.
Dec 4 '15 #3
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):
Expand|Select|Wrap|Line Numbers
  1. '...other code....
  2. Dim zHyperLink as string
  3. zHyperLink = "https://dashboard.opendns.com/stats/all/topdomains/2015-11-29.csv"
  4. Dim zWindow = "2015-11-29.csv"
  5. workbooks.open Filename:= zHyperLink
  6. Window(2015-11-29.csv).Activate
  7. 'code to copy cells to where you need
  8. '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 };-)
Dec 4 '15 #4
BHo15
143 128KB
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...
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 4 '15 #5
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 :)

Expand|Select|Wrap|Line Numbers
  1. Option Explicit '<< Check for duplicate, delete one if found
  2. '
  3. Sub loopopenbooks()
  4.     Dim zwrkbooks As Excel.Workbooks
  5.     Dim zwrkbook As Excel.Workbook
  6.     Dim zstr As String
  7.     Dim z911 As Boolean
  8. '
  9. On Error GoTo zerrtrap
  10. z911 = False
  11. '
  12.     ThisWorkbook.Activate
  13.     Set zwrkbooks = Application.Workbooks
  14.     For Each zwrkbook In zwrkbooks
  15.         MsgBox prompt:="Loop Workbook := " & zwrkbook.Name & vbCrLf & "Actvie Workbook := " & ActiveWorkbook.Name
  16.         zwrkbook.Activate
  17.         MsgBox prompt:="Loop Workbook := " & zwrkbook.Name & vbCrLf & "Actvie Workbook := " & ActiveWorkbook.Name
  18.     Next zwrkbook
  19.     '
  20. zcleanup:
  21.     If Not zwrkbooks Is Nothing Then Set zwrkbooks = Nothing
  22. Exit Sub
  23. zerrtrap:
  24.     zstr = "ErrS: " & Err.Source & vbCrLf & _
  25.         "Errn: " & Err.Number & vbCrLf & _
  26.         "ErrD: " & Err.Description & vbCrLf
  27.     If z911 Then
  28.         MsgBox prompt:=zstr, _
  29.         Buttons:=vbCritical + vbOKOnly, _
  30.         Title:="Oh Bother, Fatal error - no recovery"
  31.         Exit Sub
  32.     Else
  33.         MsgBox prompt:=zstr, _
  34.         Title:="Oh Bother, Something went wrong again"
  35.     End If
  36.     z911 = True
  37.     Resume zcleanup
  38. 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!
Dec 4 '15 #6
BHo15
143 128KB
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? :)
Dec 4 '15 #7
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...

Expand|Select|Wrap|Line Numbers
  1. Sub loopopenwindows()
  2.     Dim zwindows As Windows
  3.     Dim zwindow As Window
  4.     Dim zstr As String
  5.     Dim z911 As Boolean
  6. '
  7. On Error GoTo zerrtrap
  8. z911 = False
  9. '
  10.     ThisWorkbook.Activate
  11.     Set zwindows = Application.Windows
  12.     For Each zwindow In zwindows
  13.         MsgBox prompt:="Loop Workbook := " & zwindow.Caption & vbCrLf & "Actvie Workbook := " & ActiveWindow.Caption
  14.         zwindow.Activate
  15.         MsgBox prompt:="Loop Workbook := " & zwindow.Caption & vbCrLf & "Actvie Workbook := " & ActiveWindow.Caption
  16.     Next zwindow
  17.     '
  18. zcleanup:
  19.     If Not zwindows Is Nothing Then Set zwindows = Nothing
  20. Exit Sub
  21. zerrtrap:
  22.     zstr = "ErrS: " & Err.Source & vbCrLf & _
  23.         "Errn: " & Err.Number & vbCrLf & _
  24.         "ErrD: " & Err.Description & vbCrLf
  25.     If z911 Then
  26.         MsgBox prompt:=zstr, _
  27.         Buttons:=vbCritical + vbOKOnly, _
  28.         Title:="Oh Bother, Fatal error - no recovery"
  29.         Exit Sub
  30.     Else
  31.         MsgBox prompt:=zstr, _
  32.         Title:="Oh Bother, Something went wrong again"
  33.     End If
  34.     z911 = True
  35.     Resume zcleanup
  36. End Sub
Dec 4 '15 #8
BHo15
143 128KB
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. :)
Dec 4 '15 #9
BHo15
143 128KB
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.
Dec 4 '15 #10
BHo15
143 128KB
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.
Dec 4 '15 #11
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!
Dec 4 '15 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
0
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...
3
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...
1
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...
12
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...
1
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...
0
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 @...
0
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...
0
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...
2
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: -...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.