Posts by X82

Re: downloading from a list of URLs.

Thanks for that. That actually works almost perfectly, however:

When downloading the file, it appears in the location but it doesn't have the original name.
For example the file:
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2266772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
Actually should download "ExampleDocument.PDF"

Instead it downloads a document called:

downloadContent.aspx?documentId=2266772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true

Changing this documents extension to PDF (or DOC or whatever the original was) works fine. But it needs to download the final file name.

If this isn't possible, I can produce a list of the original file names in a sheet. Maybe the download can use that to name them?
Also, thinking along the same lines, is there a way I can specify where to download based on a column?

In our sharepoint, 5000 files are organised by category. I can make a column of this category. So the end result would be:

It downloads a file, based on the category in a column in placed the file in a specific place and depending on the filename column, renames the downloaded file.

Hi

I am after a file downloader to do a specific task using excel.

In short, we have Sharepoint. We can file our documents away to a Record Management Service, which is all fine. But trying to retrieve multiple files back once filed is an exercise in frustration, since every document has to be clicked in Sharepoint for it to then download from the RMS.

So if there is a library of 100 documents (all of which are filed to the RMS) and we need copies of these files for a USB stick or to put in another library, we have to download all 100 of them one at a time by clicking, waiting, saving and repeating.

The url for this function when we click is:

Sharepointsite.com/_layouts/RMS/Redirect.aspx?Action=GetFile&item=RMS://EDR1/Record[Extension=docx]/2403589&target=sharepointsite.com/Documents/Example.doc

In actuality, this is what happens when downloading directly from the RMS site

edr1.uk/RMS/browse/downloadContent.aspx?documentId=2403589&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true

You can see that the file id is key here. Both above URLS work on our system fine and both get the document in IE to display a open or save dialogue.

I can produce a spreadsheet with all these details In, I think. So there would be a list like

edr1.uk/RMS/browse/downloadContent.aspx?documentId=2266772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2288772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2288789&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2556472&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true

How can I get excel to take this list and download the files? If this even is possible?

I think an issue might be the fact that the URLs are not direct. They go through another process (forget the technical term). So it goes through an .aspx and then a ID to get the file. Unsure if excel vba can assist me here but I am hoping so.

So excel will try to get the document at each URL and save it to a pre-defined location.
I am unsure if there is a connection limit, so even limiting excel to downloading one at a time is still fine, since it saves the hassle of having to click each one.

This possible?

Re: Outlook: Adding drop-down menus to email content.

That sounds great, but I can't get it to work. I can't find Microsoft Windows Common Controls-2 6.0 in the references list, using Outlook 2010 on a works pc, so installing anything extra wont be possible, unless its on the system already. Also, I try to import the form using form manager, but it just throws up an error. The import window wants a cfg or fdm. When I chose all files and pick one from the zip, it just errors. "The form cannot be installed. The Key 'DisplayName' is either missing or has an incorrect value". Any advice?

Hi all

A complicated one me thinks. Basically we have to send the same template over and over each day. The main things we have to do in the body of the email is change the location to one of 6 places and add a date, usually todays. This is done with hitting reply and using a signature currently. Everyone in the email trail will need to see the response.

For example:

Hi

Your request to enter the London centre on the 22/08/2013 has been approved.

The bold above, can drop down menus be made in the email itself, so I can chose from one of the locations, have a calender day picker of sorts for the date and even have a drop down for the approved to change it to rejected (which would include more text to explain the rejection, we have a standard reply for rejections).

Obviously the sent email will have the raw data, the client would only receive London and 22/08/2013 and not a drop down menu. Just raw text.

Can this be done? Or have I asked the impossible?

A most puzzling and infuriating situation.
I have a sheet with 55 tabs. For each tab I want to run a macro,

Sub ReverseLinks() 
    Dim rng As Range, r As Range, temp As String, x As Range 
    On Error Resume Next 
    With ActiveSheet 
        Set rng = .Cells.SpecialCells(-4123) 
        For Each r In rng 
            Set x = r.Precedents 
            If x Is Nothing Then 
                temp = Mid$(r.Formula, 2) 
                If TypeName(Evaluate(temp)) = "Range" Then 
                    r.Value = r.Value 
                    Evaluate(temp).Formula = _ 
                    "='" & r.Parent.Name & "'!" & r.Address(0, 0) 
                End If 
            End If 
            Set x = Nothing 
        Next 
    End With 
    On Error Goto 0 
End Sub

I was told if I select all tabs then select all cells it would select all cells across the workbook, which it does. But when I run the macro it will only run it on the selected tab, not the other 54 as well. How can I get it to work over all of them?

Re: Merging multiple workbooks.

Thanks so much! that's perfect. Just what I was after!

I have a list of about 30 excel workbooks. Each book is set out identicle, but I was hoping if there is a way to combine all the workbooks into one?
I'm only after a specific line from each one, but rather than open each one up, find the line etc, I would just combine them all and select the single line.
Is this possible?
Or is there a way of finding certain data from each sheet, ie sheet 4, row 18(always the same name), columns 4 to 7 (contains figures).

Re: Pattern Matching? .Pattern

Thank you kind sir. Once again you have saved the day. Very much appreciated!

Re: Pattern Matching? .Pattern

You did indeed and a fantastic job it was too. I was just curious on how to modify it to find other words.
Here is the link to the new file (since it is quite long). The data I need is only one part.

Override=00 C0 B7 C7 40 29
SystemIP=172.26.72.73

Which is the MAC address and IP I am after. I have about 400 files to read. The end result would ideally be placing the file name in column A ( I can rename the config.ini file myself), the system ip in column B and the MAC address in column C. All help on this matter is greatly appreciated. But also, some insight on how it all works, the code before worked great, but it would be a bonus to know how it works, just in case I need to make tweaks or create a new script entirely.

Some very kind soul on this forum made me a great script which will scan a list of documents, and pull certain figures from that document.
I'm in need of a similar script for a different set of figures, the problem is, I have no idea how the script works.

Here is the script in question:

Sub Moncom()
    Dim myDir As String, fn As String, txt As String
    Dim myMax As Double, myName As String, myLoc As String, n As Long
    Dim myMatch As Object, x, y, temp As String, i As Long, ii As Long
    myDir = "c:\Power Readings\Monday\"
    fn = Dir(myDir & "*.txt")
    If fn <> "" Then
        n = n + 1
        Sheets(1).Cells(n, 1).Resize(, 4).Value = _
        [{"FileName","Name","Location","Max"}]
        With CreateObject("VBScript.RegExp")
            .IgnoreCase = True
            Do While fn <> ""
                myName = Empty: myLoc = Empty: myMax = 0
                txt = CreateObject("Scripting.FileSystemObject") _
                .OpenTextFile(myDir & fn).ReadAll
                .Global = True
                .Pattern = "[^\n]+"
                Set myMatch = .Execute(txt)
                .Global = False
                For i = 0 To myMatch.Count - 1
                    .Pattern = "\b(I(out)?Max|Ph ?I(.A)?)(?=\t)"
                    If .test(myMatch(i)) Then
                        temp = .Execute(myMatch(i))(0)
                        x = Application.Match(temp, Split(myMatch(i), vbTab), 0)
                        myMax = Val(Split(myMatch(i + 1), vbTab)(x - 1))
                        Exit For
                    Else
                        .Pattern = "\b(IMax Ph1)(?=\t)"
                        If .test(myMatch(i)) Then
                            temp = .Execute(myMatch(i))(0)
                            x = Application.Match(temp, Split(myMatch(i), vbTab), 0)
                            For ii = x - 1 To x + 1
                                myMax = myMax + Val(Split(myMatch(i + 1), vbTab)(ii))
                            Next
                    Else
                        .Pattern = "\b(IMax B1)(?=\t)"
                        If .test(myMatch(i)) Then
                            temp = .Execute(myMatch(i))(0)
                            x = Application.Match(temp, Split(myMatch(i), vbTab), 0)
                            For ii = x - 1 To x
                                myMax = myMax + Val(Split(myMatch(i + 1), vbTab)(ii))
                            Next
                            Exit For
                            End If
                        End If
                    End If
                Next
                .Pattern = "(\d{2}(?:[/\.])){2}\d{4}\t(\d{2}:){2}\d{2}\t[^\t]+\t([^\t]+)\t([^\t]+)"
                If .test(txt) Then
                    myName = .Execute(txt)(0).submatches(2)
                    myLoc = .Execute(txt)(0).submatches(3)
                End If
                n = n + 1
                Sheets(1).Cells(n, 1).Resize(, 4).Value = _
                Array(fn, myName, myLoc, myMax)
                fn = Dir
            Loop
        End With
    Else
        MsgBox "No file found"
    End If
    
    MsgBox "Compiling Completed Successfully"
    
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

I now have a need of scanning 400 text files, finding the text "MAC Address" and pulling the details to the right of it. But looking at the above code boggles my mind. Is there a "simple" guide which can help me in understanding how this all works? Or can anyone point me in the right direction?

Re: Importing multiple text files, live updating.

Looks like I will have to bin this idea then.
I've reached an impass. I feel so close to victory, but because the above script (because of my error) doesn't pull the correct information, I cannot rely on it.
I would really appreciate help liek you wouldn't believe!

Re: Importing multiple text files, live updating.

Quote from zaeyocla;629602

Thanks so much for all information so far.

Is confused.

Re: Importing multiple text files, live updating.

Any thoughts on this? I'm literally sitting here with no clue how to proceed.

Re: Importing multiple text files, live updating.

I'm sorry to keep bumping this thread, but I really am in a bind here. I'm really hoping someone can help on this last stage. As it stands, the code produces nearly all the numbers I need, but without the final piece, it's useless for my tasks.

Re: Importing multiple text files, live updating.

In the end I decided that I would need to get the above script working correctly before I worry about anything else.

Re: Importing multiple text files, live updating.

Any ideas on this? I'm really stuck in a bind.

Re: Importing multiple text files, live updating.

I was hoping for some more help. It seems I made an error in my original analysis.

Your above script works perfectly, but I failed to give you correct information. If you see here, File 2.rtf, only IMax B2 is in red. This was an error. Like the other file, I needed IMax B1 & IMax B2 added together to produce the final number. I only spotted it today when imputting data from the script and found the figures it produced were low compared to doing it manually. Any help in this matter is greatly appreciated. I hope this isn't too much trouble.

Re: Downloading multiple files from an FTP

Bump. Any ideas? Or is it just not possible for excel to do?

I'm currently using this script to download over 400 txt files which are needed for a project here at work. It works great, however, it will download the ftp files in question (which it gets a list of from another tab) in order. I would like a way to either download all 400 ato nce, or in a batch of 50 or even 100. As it is, the procedure takes around 70 mins to complete. I was hoping of a way to speed it up.

Here is the script I am using.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function
Sub DLTUES()
     Dim URL As String, LocalFilename As String
     Dim IP As Range
        For Each IP In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
                  URL = "ftp://user:pass@" & IP & "/data.txt"
                    LocalFilename = IP.Offset(, 2) & "\" & IP.Offset(, 1) & ".txt"
                  DownloadFile URL, LocalFilename
        Next IP
        
        MsgBox "Download Completed Successfully"
        
End Sub

All help is greatly appreciated.

Re: Running macro via a drop-down menu and button

If I open the devloper tab in excel, and go insert, I choose combo-box.
I don't mind which way of doing it, it is. But I would like the issue of not selecting the tab sorted. Currently, if someone uses the drop down, picks the right company but before they click the button they click another tab by mistake, the script will break. It will only work is the cell is selected.