0

EDIT: I found this code to download images from an xls file. Source: GET pictures from a url and then rename the picture

Code:

Option Explicit

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

Dim Ret As Long

'~~> This is where the images will be saved. Change as applicable
Const FolderName As String = "C:\Temp\"

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow '<~~ 2 because row 1 has headers
        strPath = FolderName & ws.Range("A" & i).Value & ".jpg"

        Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0)

        If Ret = 0 Then
            ws.Range("C" & i).Value = "File successfully downloaded"
        Else
            ws.Range("C" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub

It works fine with xls files, where "PIC NAME" and "URL" are in two separate columns. I have a csv file and, unlike xls, csv has it's values in one column separated by "|" (PIC NAME|URL). So - being a noob at coding - as a workaround, I used "Text to columns" in Excel to divide the lines into 2 separate columns right down the delimiter "|", that way I can use the original code.

QUESTION: is there a way to make the code work with a csv file? This would make it much faster and easier to extract images from big csv files with thousands of lines.

Community
  • 1
  • 1
Remus Sarnus
  • 25
  • 1
  • 7
  • 1
    Please remove the fluff - greetings are not to be included in the question, this is an Q&A site, not a forum. Furthermore, describe what you have tried and what your problem is. Currently, this question is not about any programming problem, but a "Please write the whole code for me", which isn't what this site is for. – Polygnome May 01 '16 at 16:35
  • Actually a suggestion from this site http://stackoverflow.com/questions/15641717/download-files-pdf-from-web1 shows exactly how to do it. – Paul Ogilvie May 01 '16 at 16:40
  • Can you tell us what your research has produced so far? A google search for "*vba download file*" led me to https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer. Have you tried that? Similarly a search for "*vba loop lines in file*" yields a lot of useful (Stack Overflow) solutions for reading a file's content. If you still have trouble with this you should rephrase your question to be more specific as to what exactly is not working for you. – Leviathan May 01 '16 at 16:41
  • @Leviathan Yes, I have updated the OP with more details of how I approached the issue. In fact, I have found a way around it. If there's a way of tweaking the original code to make it work with csv file, that would be great. – Remus Sarnus May 01 '16 at 17:42

1 Answers1

0

Since VBA provides automation for Excel everything that you can do manually also can be automated by VBA. Excel even has a macro recorder integrated that automatically converts all user action into VBA code. This answer explains how it works: Excel's Macro-Recorder usage

This can be used to see what kind of VBA code the Text to columns function generates. If you clean up the code a bit you end up with this VBA command:

Selection.TextToColumns Destination:=Range("A1") _
                      , DataType:=xlDelimited _
                      , Other:=True _
                      , OtherChar:="|"

Make sure that you replace Selection by the whole range that you imported to Excel or that you select the whole range before you execute the code. Also replace Range("A1") by the range where you want the result to be written to.

It should be noted though that it is not very efficient to write stuff into excel cells just to parse those cells afterwards. A cleaner solution to your problem would be to read the downloaded file line by line and parse each line to your needs.

Luckily there is already a solution for that: Read lines from a text file but skip the first two lines

Leviathan
  • 2,468
  • 1
  • 18
  • 24
  • I tried recording a macro as I manually applied "Text to columns" to A1 and then added the resulting code into the original script. The whole process is not particularly fast. I'll look into the alternative solution you've suggested and see if I can come up with a more efficient mod to the original code. – Remus Sarnus May 01 '16 at 18:22