Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: VBA to add vba
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Oct 2018
    Posts
    506
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to add vba

    brilliant! So now, the last question- can you do the same thing basically to make/show buttons and hide them when a cell in that row is empty (let's say the URL cell) and have that set to run when the workbook opens?

  2. #12
    Board Regular
    Join Date
    Oct 2018
    Posts
    506
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to add vba

    Actually this goes to two questions-1. was the buttons

    2. With this code, can this macro also self-repeat. I guess meaning each row will have it's own button to run each download individually but also thinking that a single button (at the top of the activesheet) that could be clicked to run every visible button would be sweet too. Make sense?

    Thanks again @Norie

  3. #13
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA to add vba

    Why not have one button that goes down the rows and if there is data in a row does the download, if there isn't it skips the row?
    If posting code please use code tags.

  4. #14
    Board Regular
    Join Date
    Oct 2018
    Posts
    506
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to add vba

    That would be great. However, the reason I was setting up for both was merely because the files, despite being small, are being downloaded via a very slow, intermittent satellite connection- So I wanted such that if it got halfway down and failed, I could go back and download the failed. Does that make sense? So continuing to tweak your code, here's what I have so far. What do you think?

    Note: I guess these buttons need to line up perfectly with the cells, right? Is there a specific way I should be making them to line up with the cells? In the past I've just made them dimensional until they look good....

    Note: I have an error here with the method for .SaveAs that I haven't overcome in my mind. I might be putting in a "transfer" piece of code instead... Welcome to ideas though. I tried to spell out each procedure here.
    Code:
    Sub Download()Dim URL As String
    Dim tstamp As String
    Dim Folder0 As String
    Dim Folder1 As String
    Dim Namer As String
    Dim Date0 As String
    Dim Date1 As String
    Dim LocalFilePath As String
    Dim TempFolderOLD As String
    Dim TempFileNEW As String
    Dim DownloadStatus As Long
    Dim btn As Shape
    Dim rw As Long
    
    
    With Sheets("Background")
        Set btn = .Shapes(Application.Caller)
        rw = btn.TopLeftCell.Row 'Should be the row the button that has been clicked on***
        Namer = .Range("B" & rw) 'Pub name
        URL = .Range("I" & rw)   'URL to download
        Date0 = .Range("E" & rw) 'Week #
        Date1 = .Range("C" & rw) 'Year #
    End With
    With Sheets("Setup")
        Folder0 = .Range("B5") 'temp file
        Folder1 = .Range("B7") 'permanent file
    End With
    TempFolderOLD = Environ("Userprofile") & "\" & Folder0
    tstamp = Format(Now, "mm-dd-yyyy")
    TempFileNEW = TempFolderOLD & tstamp & "\" & Namer & ".pdf"
    LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\" & Namer & ".pdf"
        
    'If these criteria are met, let's begin the download tree
        If Date1 <> Sheets("Background").Range("G2") And Date0 <> Sheets("Background").Range("I2") Then
           
    'Let's assign everything to the temp folder
        'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
            If Len(Dir(TempFolderOLD)) <> "" Then Kill (TempFolderOLD)
        'Make a new temp folder
            If Len(Dir(TempFolderOLD)) = "" Then MkDir (TempFolderOLD)
        'Attempt download to the temp folder
            DownloadStatus = URLDownloadToFile(0, URL, TempFileNEW, 0, 0)
        'Check for proper download
            If DownloadStatus = 0 Then
            'Delete the old files
                Kill (LocalFilePath)
            'Save temp files to replace old files
                TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF
            'Now delete temp files
                Kill (TempFileNEW)
            'Now update excel sheet to show download passed
                MsgBox "File Downloaded. Check in this path: " & LocalFilePath
                    With Sheets("Background")
                        .Range("F" & rw) = tstamp
                        .Range("G" & rw) = "SAT"
                        .Range("C" & rw) = Format(Now, "ww", vbWednesday)
                        .Range("E" & rw) = Format(Now, "yy")
                    End With
        'If download failed, update excel to show- old files should NOT have been deleted yet but the temp file should be deleted
            Else:
                MsgBox "Download File Process Failed"
                Sheets("Background").Range("G" & rw) = "FAIL"
                Kill (TempFolderOLD)
            End If
        'If the original criteria were met and the download was not necessary, say so
        Else: MsgBox "The most up to date pub has been downloaded"
        End If
    End Sub

  5. #15
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA to add vba

    Couldn't you use column G to indicate if the download has to be attempted?

    If it's 'FAIL' or empty try the download, if it's 'SUCCESS' don't try the download.
    If posting code please use code tags.

  6. #16
    Board Regular
    Join Date
    Oct 2018
    Posts
    506
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to add vba

    Quote Originally Posted by Norie View Post
    Couldn't you use column G to indicate if the download has to be attempted?

    If it's 'FAIL' or empty try the download, if it's 'SUCCESS' don't try the download.
    Eh- well I used it the way I did because each week an update comes out. If the most current version (same file name) is downloaded as indicated in the Date1 check, then it will not download. That part all worked. My issue was coming in with

    1. Do I use cmd buttons and have them "not visible" for the unused rows or is there a way to have the sheet dynamically add buttons when it opens if one column of cells is not empty. (e.g. if C1 is not empty, show button, If c2 is empty, keep button hidden).

    2. My bigger problem- How do I properly write the piece below (it's in the middle of my big code)
    Code:
    'Save temp files to replace old files.            TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF

  7. #17
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA to add vba

    Why do you want to use multiple command buttons?

    Why not have one command button that goes down the list and downloads file if it need's to be downloaded?

    With that approach you wouldn't need multiple buttons, something like this could work.
    Code:
    Sub Download()
    Dim URL As String
    Dim tstamp As String
    Dim Folder0 As String
    Dim Folder1 As String
    Dim Namer As String
    Dim Date0 As String
    Dim Date1 As String
    Dim LocalFilePath As String
    Dim TempFolderOLD As String
    Dim TempFileNEW As String
    Dim DownloadStatus As Long
    Dim LastRow As Long
    
    Dim rw As Long
    
        ' find last row of data in column B on 'Background'
        LastRow = Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
    
        ' loop through rows on 'Background'
        For rw = 4 To .Range("B" & Rows.Count).End(xlUp).Row
        
            With Sheets("Background")
                Namer = .Range("B" & rw)    'Pub name
                URL = .Range("I" & rw)   'URL to download
                Date0 = .Range("E" & rw)    'Week #
                Date1 = .Range("C" & rw)    'Year #
            End With
            
            With Sheets("Setup")
                Folder0 = .Range("B5")    'temp file
                Folder1 = .Range("B7")    'permanent file
            End With
            
            TempFolderOLD = Environ("Userprofile") & "\" & Folder0
            tstamp = Format(Now, "mm-dd-yyyy")
            TempFileNEW = TempFolderOLD & tstamp & "\" & Namer & ".pdf"
            LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\" & Namer & ".pdf"
    
            'If these criteria are met, let's begin the download tree
            If Date1 <> Sheets("Background").Range("G2") And Date0 <> Sheets("Background").Range("I2") Then
    
                'Let's assign everything to the temp folder
                'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
                If Len(Dir(TempFolderOLD)) <> "" Then Kill (TempFolderOLD)
                'Make a new temp folder
                If Len(Dir(TempFolderOLD)) = "" Then MkDir (TempFolderOLD)
                'Attempt download to the temp folder
                DownloadStatus = URLDownloadToFile(0, URL, TempFileNEW, 0, 0)
                'Check for proper download
                If DownloadStatus = 0 Then
                    'Delete the old files
                    Kill (LocalFilePath)
                    'Save temp files to replace old files
                    TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF
                    'Now delete temp files
                    Kill (TempFileNEW)
                    'Now update excel sheet to show download passed
                    MsgBox "File Downloaded. Check in this path: " & LocalFilePath
                    
                    With Sheets("Background")
                        .Range("F" & rw) = tstamp
                        .Range("G" & rw) = "SAT"
                        .Range("C" & rw) = Format(Now, "ww", vbWednesday)
                        .Range("E" & rw) = Format(Now, "yy")
                    End With
                    
                    'If download failed, update excel to show- old files should NOT have been deleted yet but the temp file should be deleted
                Else:
                    MsgBox "Download File Process Failed"
                    Sheets("Background").Range("G" & rw) = "FAIL"
                    Kill (TempFolderOLD)
                End If
                'If the original criteria were met and the download was not necessary, say so
            Else
    
                MsgBox "The most up to date pub has been downloaded"
            End If
            
        Next rw
    
    End Sub
    If posting code please use code tags.

  8. #18
    Board Regular
    Join Date
    Oct 2018
    Posts
    506
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to add vba

    I guess the reason for multiple is so that on rare occasions the user is able to begin the download of a specific pdf first if they need to.

    Further, I'm using some of your code here to modify a "view" where the macro opens the downloaded file so it can be viewed.

  9. #19
    Board Regular
    Join Date
    Oct 2018
    Posts
    506
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to add vba

    So I'm kicking an error "control variable already in use" and it's highlighting the "for rw = 4" line

    Code:
    Sub Downloadx()Dim URL As String
    Dim tstamp As String
    Dim Folder0 As String
    Dim Folder1 As String
    Dim Namer As String
    Dim Date0 As String
    Dim Date1 As String
    Dim LocalFilePath As String
    Dim TempFolderOLD As String
    Dim TempFileNEW As String
    Dim DownloadStatus As Long
    Dim LastRow As Long
    Dim Finalname As String
    Dim btn As Shape
    Dim MyFSO As FileSystemObject
    Set MyFSO = New Scripting.FileSystemObject
    
    
    Dim rw As Long
    
    
        ' find last row of data in column B on 'Background'
        LastRow = Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
    
    
        ' loop through rows on 'Background'
        For rw = 4 To Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
        
            With Sheets("Background")
                Namer = .Range("B" & rw)    'Pub name
                URL = .Range("I" & rw)   'URL to download
                Date0 = .Range("E" & rw)    'Week #
                Date1 = .Range("C" & rw)    'Year #
                For rw = 4 To .Range("B" & Rows.Count).End(xlUp).Row
            End With
            
            With Sheets("Setup")
                Folder0 = .Range("B5")    'temp file
                Folder1 = .Range("B7")    'permanent file
            End With
            
            TempFolderOLD = Environ("Userprofile") & "\" & Folder0
            tstamp = Format(Now, "mm-dd-yyyy")
            TempFileNEW = TempFolderOLD & tstamp & "\" & Namer & ".pdf"
            LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\"
            Finalname = Namer & ".pdf"
            'If these criteria are met, let's begin the download tree
            If Date1 <> Sheets("Background").Range("G2") And Date0 <> Sheets("Background").Range("I2") Then
    
    
                'Let's assign everything to the temp folder
                'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
                If Len(Dir(TempFolderOLD)) <> "" Then Kill (TempFolderOLD)
                'Make a new temp folder
                If Len(Dir(TempFolderOLD)) = "" Then MkDir (TempFolderOLD)
                'Attempt download to the temp folder
                DownloadStatus = URLDownloadToFile(0, URL, TempFileNEW, 0, 0)
                'Check for proper download
                If DownloadStatus = 0 Then
                    'Delete the old files
                    Kill (LocalFilePath)
                    'Save temp files to replace old files
                    'TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF
                    MyFSO.CopyFile Source:=TempFileNEW, Destination:=LocalFilePath & Finalname
                    'Now delete temp files
                    Kill (TempFileNEW)
                    'Now update excel sheet to show download passed
                    MsgBox "File Downloaded. Check in this path: " & LocalFilePath
                    
                    With Sheets("Background")
                        .Range("F" & rw) = tstamp
                        .Range("G" & rw) = "SAT"
                        .Range("C" & rw) = Format(Now, "ww", vbWednesday)
                        .Range("E" & rw) = Format(Now, "yy")
                    End With
                    
                    'If download failed, update excel to show- old files should NOT have been deleted yet but the temp file should be deleted
                Else:
                    MsgBox "Download File Process Failed"
                    Sheets("Background").Range("G" & rw) = "FAIL"
                    Kill (TempFolderOLD)
                End If
                'If the original criteria were met and the download was not necessary, say so
            Else
    
    
                MsgBox "The most up to date pub has been downloaded"
            End If
            
        Next rw
    
    
    End Sub

  10. #20
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA to add vba

    Why do you have this line of code?
    Code:
                For rw = 4 To .Range("B" & Rows.Count).End(xlUp).Row
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •