Hyperlink Download (1 Viewer)

[SikSlk]

Hi,

I think this is only my second post (found i have made a few, lol) in over a year because I can usually find what im after on this site or I just hack stuff together till it works, lol.

Anyway my question:

How can I download a hyperlink (its an excel spreadsheet, CSV file to be exact i believe) to a certain place and name?​

I wish to do this roughly once a day for about 10-14 files. then these will be imported to the database. but first thing will be a fairly automated downloader if possible.

any third party software will be fine but id rather have access do it with a code behind a button etc.

Thankyou for any/all suggestions.

 

[boblarson]

Hi,

I think this is only my second post (found i have made a few, lol) in over a year because I can usually find what im after on this site or I just hack stuff together till it works, lol.

Anyway my question:

How can I download a hyperlink (its an excel spreadsheet, CSV file to be exact i believe) to a certain place and name?​

I wish to do this roughly once a day for about 10-14 files. then these will be imported to the database. but first thing will be a fairly automated downloader if possible.

any third party software will be fine but id rather have access do it with a code behind a button etc.

Thankyou for any/all suggestions.

I found that I needed to use the INet control to do it and I can post how I did it, if I can get a copy of my code from someone at my old work. I forgot to keep a copy of the download code.

 

[SikSlk]

Thanks so much for the quick reply... if you have anything to start em off that would be great. Off to google INet control and see if I can find anything on it.

Thankyou... (you have helped me in the past via other peoples posts thanks for those as well)

 

[SikSlk]

no luck so far... using 2007 and when using the following

http://support.microsoft.com/kb/163653

I cant find insert active x control. This is my first attempt on 07 but i even switched back to 03 and couldn't find 'Microsoft Internet Control' Under the active x.

Is this only for 97?

 

[boblarson]

no luck so far... using 2007 and when using the following

http://support.microsoft.com/kb/163653

I cant find insert active x control. This is my first attempt on 07 but i even switched back to 03 and couldn't find 'Microsoft Internet Control' Under the active x.

Is this only for 97?

No, I used it in 2000.

 

[SikSlk]

ok next iv tried Openfile which i ahve used in 03 a few times only in 07 it give the error

compile error:

sub or function not defined

Here is the code which i have borrowed of somewhere in this forum:

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Me.tbhidden.SetFocus

If IsNull(tbfile) Or tbfile = "" Then
MsgBox "Please browse and select a valid file to open.", vbCritical, "Invalid File"
Else
OpenFile (tbfile)
End If

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Command2_Click

End Sub

 

[SikSlk]

Ok... an update... I have made a slight advance. Have got the open file working and it was to do with the module not been fully done (ie i didn't copy paste the whole lot)

 

[boblarson]

Oh, I made a mistake - it was the Microsoft Internet Transfer control that I used.

 

[SikSlk]

I have just found your post which you are trying to achieve the very thing im chasing.

http://www.access-programmers.co.uk/forums/showthread.php?t=118535&highlight=save+file+location

im working off this example.

I think I may have it working. Need more testing first but its getting closer.

 

[SikSlk]

Have got it finished and working. Access 2007.
I have a button on continuous form which has the following code.
I have copied the Inet Controll out of the example in the above link next to the button. Not sure if it has to be each record but it is now and its working

"clink" is a hyperlink but note that i had to turn of teh hyperlink feild type in the database properties.
"datanm" is the given title for the hyperlink that will be used when making the file.
"status" also added a status which is set to ready when form is opened. just so when people press the button it will change from ready and stop them trying to download to quickly before the first has been finished.

just thought I'd post it up just incase anyone else needed, hope it makes it easier for someone in the future.

Private Sub download_Click()

Dim X As Integer
Dim B() As Byte
Dim sUrl As String
Dim clink As String
Dim Dataname As String

If Status = "ready" Then 'check if sheet is ready to download

Status = "Downloading"

clink = CSVlnk
Dataname = dataNm

'Open image url
B() = Inet1.OpenURL("" & clink & "")

'Open for a new file
'Open "c:\import\" & dataNm & ".csv" For Binary As #1 'can beused to save to anylocation for example its currently set to save in c:\import\
Open CurrentProject.Path & "\" & dataNm & ".csv" For Binary As #1 'save in same location as database

Put #1, , B()
Close #1
Do
DoEvents
'Loop while still downloading
Loop While Inet1.StillExecuting
DoEvents
MsgBox "" & dataNm & "Done"
Status = "Ready"
Else
If Status = "Downloading" Then
MsgBox "Please wait for download of last file to finish"
Else
MsgBox "Unknown error please check teh status of teh form is 'Ready', or try reopening this form"
End If
End If

End Sub

 

[SikSlk]

every now and then (most of the time now) it seems to only get about 10 records of the file. Usually there should be anything up too 1000 records.

boblarson can you see anywhere i can alter it? i can post my new code should it help?

 

[boblarson]

i can post my new code should it help?

It might help.

 

[SikSlk]

Here is the latest code. It is supposed to do download the CSV file and then Convert it to XLS. The conversion process seems to be fine, I even removed it and used the original code to check if the csv file was been downloaded properly and when i saw the csv file was only containing 10 (10.5 really ) so im not sure if i have added code that has interrupted the downloading process itself?
is there a wait command?
max file i have seen is about 90kb or there abouts, and it had about 1010 records or so.

I thought I was so close

Private Sub download_Click()

Dim X As Integer
Dim B() As Byte
Dim sUrl As String
Dim clink As String
Dim Dataname As String

If Status = "ready" Then 'check if sheet is ready to download
    If Ddiff <= 1 Then 'check if more then 1 hour has passed
    MsgBox "this file cant be downloaded since it has already been done within the last hour."
    Exit Sub
    Else

Status = "Downloading"

clink = CSVlnk
Dataname = dataNm
 
 'Open image url
 B() = Inet1.OpenURL("" & clink & "")
 
 'Open for a new file
 'Open "c:\import\" & dataNm & ".csv" For Binary As #1
 Open CurrentProject.Path & "\" & dataNm & ".csv" For Binary As #1 'save in same location as database
 
 Put #1, , B()
 Close #1
 Do
 DoEvents
  'Loop while still downloading
  Loop While Inet1.StillExecuting
 DoEvents
 LastDLtb = (Now)
 Filestate = "CSV"
 MsgBox "" & dataNm & "Downloaded, Please wait as it Now converts to XLS.  Press 'ok' to continue."
 Status = "Ready"
 
'''''''''''' covert to XLS file '''''''''''''''''''
        If Status = "Ready" Then
            Status = "Converting"

            If Status = "Converting" Then
                If Filestate = "CSV" Then
                ConvertCSVtoXL CurrentProject.Path & "\" & dataNm & ".csv"
                Else
                MsgBox "File already converted, Please wait until next file is downloaded."
                Status = "Ready"
                End If
            Else
            End If
        Else
        MsgBox "Please wait till previous conversion precess has finished"
        End If
''''''''''' end of conversion '''''''''''''''''''''

    End If 'this end if is for the time check

Else
    If Status = "Downloading" Then
    MsgBox "Please wait for download of last file to finish"
    Else
    MsgBox "Unknown error please check teh status of teh form is 'Ready', or try reopening this form"
    End If
End If

End Sub

 

[SikSlk]

should this be moved to another section? Ie code section?

I cant find anything on the net about this problem either, anyone got any ideas at all?

 

[boblarson]

should this be moved to another section? Ie code section?

I cant find anything on the net about this problem either, anyone got any ideas at all?

That would be fine if you wanted to post the question there. I'm not sure, myself, as to what would be going on. I've only limited experience with using similar code for doing this. Sorry I can't be more help.

 

[SikSlk]

Ok...Finally I have success... Ripped out the guts of the program started from a different direction which was found:

http://www.mvps.org/access/modules/mdl0037.htm

I had to copy the class modules over manually...

rewrote a few lil bits of the code and hay presto... downloading the full file again. Had to check one reference also... i believe visual basic. (will be in the above link). What i found odd was computer at home wouldn't have a problem getting the full file yet at work it would fail or stop at random points at roughly the 10-12 record mark. check references were the same and both computers running same windows/office/IE/vb6/visual studio. No idea other then the program didn't been at work like me

wanted to post a link to my solution just in case anyone had a problem.