Posts by JerryD

    I need code to download an Excel file from the internet. I use the following code to connect to the site. Once at the site, Excel files are accessible by clicking individual download links which provide a "File Download Form" with "Open Save Cancel" buttons. I need code which will bypass this form and perform a Save As in "C:\Files\" and then close the IE. I only need code for one file (ex: href="ambulancefee0407.xls"). There are many files to choose from but an example for this one file will be sufficient.


    Can someone please fill in the code?


    Thanks,


    JerryD


    Re: Pull Web Page Into Worksheet


    Thanks to Norie, Tom, Ivan, and Dave for your responses. All your suggestions work fine.
    Here is Norie's Test code modified with Ivan's ExecWB commands, which work fine for me.
    This code will copy the Web page to the Clipboard and then paste the clipboard into Excel
    (the PasteSpecial command will paste only the text, omitting links and objects)


    Thanks again,
    Jerry D


    Re: Internet Explorer Select All


    OK, let's try "http://www.aarp.org/". I would like to have all the text on that page copied to a sheet in my workbook. I think the best way to illustrate is to have you perform the steps manually and see the results. Go to "http://www.aarp.org/". Right-click on any blank area of the page (pop-up menu) and then click "Select All". Then right-click again i(pop-up again) and click "Copy" (put it in the clipboard).


    Then go to a blank Excel sheet and right-click in A1 (pop-up) and click "Paste Special". Then choose "Text" and click OK. The resulting text, now on your Excel sheet, is what I'm after. I'm not looking for Tables or lists or anything, just "all" the text. Once I get the text in my workbook, I will be able to handle the code which does the analysis.


    Thanks,


    JerryD

    Re: Internet Explorer Select All


    Thanks for the responses.


    What I want to do is to copy the "entire page" to the clipboard so that I can Paste Special (Text) in Excel. What I do now is manually "right click" on the web page and then select "Select All" and then right click again and click "Copy". I would like to automate that process. Any method would be satisfactory with me.


    JerryD

    I am trying to write VBA code which will go to a web site, SelectAll, make a Copy and then close the IE connection. In my code, Set objIE = CreateObject("InternetExplorer.Application") works, objIE.Navigate "https://www.whatever.com" works, and objIE.Visible = True works. What's the objIE code for "Select All" and "Copy" ? I can't seem to find any reference to these commands in the posts anywhere.


    Tks


    JerryD

    I have a macro which cycles through maybe 2 dozen text files and imports data into a workbook. The first execution is fairly fast. Each time I execute the macro after that, it seems to run at about half the speed as the preceeding time. If I close the workbook and reopen, the macro will run at the original fast speed, but will again slow down with each execution. Can anyone explain what is happening and how to correct it?


    TIA


    JerryD

    The following VBA code will return the File DateTime stamp of the pathname specified within the quotes.


    fdt = FileSystem.FileDateTime("c:\\qc\nploc.c")


    Is there a similar VBA one liner which will return the file size?



    TIA


    JerryD

    Re: Force Save As To Display A Specific Folder


    Can you force Save As (inside Excel) to always display the same folder when opening? Something like putting the code in Auto_Open so that when you do select Save As, it display's the same folder everytime?

    When downloading files from the net (Excel open or not), a Save As dialog box opens with a folder selected (but it's not always the same folder). Can I use Excel (VBA or otherwise) to force this Save As box to display the same specific folder everytime? Thanks for you help.

    Re: IF and IS function


    Hey


    Try a nested IF. Something like: =IF(condition = "", "", IF(condition, true, false)). I may not have the parentheses in the right place below but it should work.


    =If((ISNONTEXT(SEARCH("/",$S16)),$V$200-$S16) = "", "", IF(ISNONTEXT(SEARCH("/",$S16)),$V$200-$S16,""))


    HTH


    JerryD

    I extracted the code listed below from a JWalkenback Excel tip (see http://www.j-walk.com/ss/excel/tips/tip86.htm). It sends email OK - I'm using Excel 2000. However, it momentarily displays the Outlook Express application during execution. How can I modify the code to keep Outlook Express in the background so that it does not flash on the screen?


    TIA


    JerryD

    Re: Opening file with shell() command


    Hey Mullen:


    Just ran across your post. I have had some experience with Shell and DOS. DOS can sometimes be set to have "short" filenames and foldernames. By this I mean that DOS reduces all names to 8 characters, the first 6 of the file/folder name and a curlycue# - while ignoring spaces. Thus, a filename of "Databas Application Program.mdb" becomes just "Databa~1.mdb".
    Folder "Just Quarterly" would become "JustQu~1" (ignoring the space).


    You would do this for every filename/foldername in the pathname (address). The # could be 1-n depending (if there are other file/folder names whose first 6 characters are the same as other names).


    Something like the followng code: (this will use Notepad to open a file called "Database Application Program.txt" located in a folder called "Just Quarterly", etc. You can use vba to create Fname (using your folder and file names) - or you can just specify Fname = "" and insert desired text between the quotes.


    Dim OpenDW As String, Fname As String
    Fname = "notepad.exe C:\Excel\JustQu~1\Databa~1.txt"
    OpenDW = Shell(Fname, 1)


    Just a thought. HTH (working with DOS can be a pain in the DOS)


    JerryD

    Re: Focus


    Thanks for the replies. The object is to keep the focus in a userform text box, so that the textbox is ready to receive data, even if someone inadvertently clicks an option that is already selected (which can happen and which apparently then removes the textbox focus).


    Bill Rockenbach: The dual buttons setting on top of each other is a neat trick. And it does keep the focus in the textbox, which is desirable. But by hiding the other button, it then does not show the option as being selected (the "dot" goes with the hidden button). Any way of selecting two options at the same time (in same frame) so that the option (in this case) would appear as selected? (or maybe something like transparent frames on top of each other with identical dual buttons?)


    Thanks


    JerryD

    I have created a userform (userform1) with several option buttons and a textbox along with a couple of command buttons. My objective is to click option 1 and have the blinking insertion point be in the textbox "after" I click the option. So I set the focus to the textbox within the Optionbutton click sub as shown:

    Code
    Private Sub OptionButton1_Click()
        TextBox1.SetFocus
    End Sub


    Works fine if I only click the option button once. However, If I click the same option button twice in succession, the blinking insertion point disappears. I guess I lose focus. What do I add to the code to have the insertion point remain in the textbox, blinking, no matter how many times I click the same option?


    TIA


    JerryD

    Re: Multi viables on same line


    Thanks for the reply. I had tried everything except the colon I guess.


    My reason: reduce the number of lines when printing hard copy of code containing a large number of variables.


    Thanks again


    JerryD