Download file from url and import to table

[james_halliwell]

HI,

please could someone help me out, i am trying to download an excel document from a url and the import it into a table i can successfully download the file to my desktop but when i try to import it i get the below error

the Microsoft office access database engine cannot open or write to file 'C:\documents and settings\JHalliwe\Desktop\' it is already opened exclusively by another user, or you need permission to view and write its data

i have checked and the excel book is not open ( i think its trying to open while its still downloading is there away around this

many thanks (code below is what i am using)

Dim strUserName As String
strUserName = Environ("UserName")
Dim FolderLoc As String
FolderLoc = "C:\Documents and Settings\" & strUserName & "\Desktop\"
Dim StFile As String
Dim MySelect
MySelect = Forms!FrmIndex!Text134

If MySelect = "Joints" Then
StFile = Dir$("C:\Documents and Settings\" & strUserName & "\Desktop\DO_GS1.xls")
ElseIf MySelect = "Codman" Then
StFile = Dir$("C:\Documents and Settings\" & strUserName & "\Desktop\COD_GS1.xls")
End If


DoCmd.OpenForm "FrmLoading", acNormal

Dim myURL As String
myURL = "http://www.commerce.jnjgateway.com/pdfs/GS1/DO_GS1.xls"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile (FolderLoc & "DO_GS1.xls ")
    oStream.Close
End If


CurrentDb.Execute "DELETE * FROM TblGS1Conversion_temp"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TblGS1Conversion_temp", "C:\Documents and Settings\" & strUserName & "\Desktop\" & StFile, True

 

[pr2-eugin]

Just AIR CODED.. Try..

If MySelect = "Joints" Then
    StFile = Dir$("C:\Documents and Settings\" & strUserName & "\Desktop\DO_GS1.xls")
ElseIf MySelect = "Codman" Then
    StFile = Dir$("C:\Documents and Settings\" & strUserName & "\Desktop\COD_GS1.xls")
End If

DoCmd.OpenForm "FrmLoading", acNormal

Dim myURL As String
myURL = "http://www.commerce.jnjgateway.com/pdfs/GS1/DO_GS1.xls"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile (FolderLoc & "DO_GS1.xls")
    oStream.Close
End If

[COLOR=Blue]Do While FileLocked(FolderLoc & "DO_GS1.xls")
    DoEvents
Loop[/COLOR]

CurrentDb.Execute "DELETE * FROM TblGS1Conversion_temp"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TblGS1Conversion_temp", "C:\Documents and Settings\" & strUserName & "\Desktop\" & StFile, True

Where FileLocked is a method to test if it Locked by other process obtained from MS Support Article.

Althought I would change it as,

Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
[COLOR=Green]   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.[/COLOR]
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   [COLOR=Green]' If an error occurs, the document is currently open.[/COLOR]
   If Err.Number <> 0 Then
      FileLocked = True
      Err.Clear
   End If
End Function

That is just removing the MsgBox.

 

[james_halliwell]

This is brilliant !! fantastic many thanks for taking the time to read and help me out with my problem!!!!!!!!!!!!!!!!!!

 

[pr2-eugin]

Glad to help.. Good Luck..