Import into access help needed

[Asghaar]

Hello all,

At work I access my files thou an address in the browser like http://"ip address"/something/something2/ - here are some xls files ( this is a dummy link since i can't say the exact address ).
From excel I can access this link and download a certain xls file that is generated there by boxi .
I want to know if i can do something similar in Access,as I don't like to use excel for this?
The reason is that if i can add the xls files found there to acces i could automate some reports.

Thank you all for the help.


Have a great day.



Kind regards

 

[Pat Hartman]

The code you are using in Excel will probably work in Access. Have you tried it? Post the code and we'll tell you if it has to change.

 

[Asghaar]

Hello Pat Hartman,

Thank you for the reply and sorry for the delay.
Here is the code used in excel:

Kill "\\dakar\SSPO\L1\Customer Care\Reports\Tickets_CC_daily.xls"

Workbooks.Open Filename:= _
        "http://something/ExtractBOXI/something2/Tickets_CC_daily.xls"

               
        Windows("Tickets_CC_daily.xls").Activate
      
        ActiveWorkbook.SaveAs "\\one\two\three\Customer Care\Reports\Tickets_CC_daily.xls"
            
        Windows("Tickets_CC_daily.xls").Close SaveChanges = True

This is run based on a windows task that opens an xls file containing this sub in the auto_open event.
Unfortunately this sometimes work and sometimes i get interrupted code without a reason specified but as the line that caused the problem being

Windows("Tickets_CC_daily.xls").Activate

That's why i hope that with access to find a better way.

Thank you for the help.

 

[Pat Hartman]

It looks like you are just copying a file from one location to another. If that is the case, there is no need to automate Excel at all. Just use the File System Object (FSO) to copy the file.

 

[Asghaar]

Hello,


I've tried to use FSO with the code below :

Dim fso As New FileSystemObject
fso.CopyFile "http://wsdfarm-brise-something/ExtractBOXI/Referentiel%20MSP/Tickets_CC_daily.xls", "\\dakar\soemthing\something2\works.xls", True

But it i get the following error : " Run-time error '52': Bad file name or number "
I mention that i had set the reference to Microsoft Scripting Runtime.

Any ideas?

 

[pwbrown]

Maybe you need to explain exactly what you are trying to do.
When you import the excel file are you doing anything to it?

 

[Asghaar]

Hello,

In excel the logic is at follows : open the workbook form the link - make it active - save active workbook as - to my local/network location.
I want to do something similar but in Access And not in excel because i interrupt errors sometimes at the lines that should make the workbooks active or when the sub is called in the open event of the excel.
And I hope that in access i can get rid of this problems.
I've tried the recommended solution with FOS and also with the vba function FileCopy..but it seems that is not working - the link is seen as bad although in Excel works.

Hope I've managed to bring some light on this.

 

[pwbrown]

Try:

FileSystemObject.CopyFile "http://wsdfarm-brise-something/ExtractBOXI/Referentiel%20MSP/Tickets_CC_daily.xls", "\\dakar\soemthing\something2\works.xls"

If it doesn't work check your links are correct. You don't need to have true at the end because true is default.

 

[Asghaar]

Hi,

I've tried

Private Sub Command0_Click()
FileSystemObject.CopyFile "http://wsdfarm-brise-something/ExtractBOXI/Referentiel%20MSP/Tickets_CC_daily.xls", "\\dakar\soemthing\something2\works.xls"
End Sub

,but I get the following error - Runtime error 424 - >Object required .
I have the Microsoft Scripting Runtime Reference added.
Also the links if i try to open them separate:
http://wsdfarm-brise-something/ExtractBOXI/Referentiel MSP/Tickets_CC_daily.xls - in a browser works
\\dakar\soemthing\something2\- in windows explorer works

 

[pwbrown]

Tested and this works perfect:

Private Sub Command0_Click()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile "http://wsdfarm-brise-something/ExtractBOXI/Referentiel%20MSP/Tickets_CC_daily.xls", "\\dakar\soemthing\something2\works.xls"
End Sub

 

[Asghaar]

Hello all,


I managed to find out why it didn't worked...because i needed something that would replicate wget (linux) and download some web filles (http,in my case).
Unfortunately i didn't understood or explained very well what i wanted.

I'm realy sorry for everyone that lost time with my post and once again many thanks to the ones that tried to help me.

*below find the code that i'm using and that works as i would like :

Private Sub Command2_Click()

On Error GoTo errHere
 
Dim strQRY As String
 
Dim strHTTP As String
Dim strFileToSave As String
 
strHTTP = "your link file with full path and file name"
strFileToSave = "save path full with file name"
 
If fnDownloadHTTP(strHTTP, strFileToSave) = False Then      ' -- downlaod the file
    MsgBox "File DL failed. Make sure folder exist"
    GoTo ExitHere
End If
 
MsgBox "Thumbs up :-)"
 
ExitHere:
    Exit Sub
errHere:
    MsgBox "Error"
    Resume ExitHere
End Sub
 
Public Function fnDownloadHTTP(strTarget As String, strSaveAs As String, Optional strUN As String, Optional strPW As String) As Boolean
On Error GoTo errHere
 
Dim xmlHTTP As Object
Dim strRespText As String
fnDownloadHTTP = True
Set xmlHTTP = CreateObject("Microsoft.XMLHTTP")
With xmlHTTP
    .Open "GET", strTarget, False, strUN, strPW
    .setRequestHeader "cache-control", "no-cache,must revalidate"
    .Send
    If fnSaveDownloadFile(strSaveAs, .responseBody) = False Then
        GoTo errHere
    End If
End With
 
ExitHere:
    Set xmlHTTP = Nothing
    Exit Function
 
errHere:
    fnDownloadHTTP = False
    Resume ExitHere
End Function
 
Private Function fnSaveDownloadFile(strFilePath, bytArray) As Boolean
On Error GoTo errHere
 
 
Dim objStream  As Object 'New ADODB.Stream
fnSaveDownloadFile = True
Set objStream = CreateObject("ADODB.Stream")
With objStream
    .Type = 1 'adTypeBinary
    .Open
    .Write bytArray
    .SaveToFile strFilePath, 2 'adSaveCreateOverWrite
End With
 
ExitHere:
    Exit Function
errHere:
    fnSaveDownloadFile = False
    Resume ExitHere
End Function