downloading an excel file from a page

ortizmario6025…

Forums

the macro downloads the file in Excel but when going to the ExcelImport routine it throws an error that says "initial load error", what is wrong with this routine? If the file downloads fine. Thanks 
remove the http: so that the address does not stand out
Sub MainIgnoreWarning(true)
Call DownloadFile("//cdn.bancentral.gov.do/documents/estadisticas/mercado-cambiario/documents/TASA_DOLAR_REFERENCIA_MC.xlsx" , client.workingdirectory & "\Archivos fuente.ILB\"  & "TASA_DOLAR_REFERENCIA_MC.xlsx") 
'Importando el archivo a IDEA
Call ExcelImport()'Eliminando Columnas ExtrasCall RemoveField() Call RemoveField1()Call RemoveField2()Call ModifyField()
'limpiando los encabezados erróneos
Call DeleteRecords("TASA_DOLAR_REFERENCIA_MC-Diaria.IMD","Tasa Diaria del Dolar.IMD","1","3")Client.RefreshFileExplorer
End Sub
Sub DownloadFile(url As String, filePath As String)Dim WinHttpReq As Object, attempts As Integerattempts = 15On Error GoTo TryAgainTryAgain:attempts = attempts -1Err.ClearIf attempts > 0 ThenSet WinHttpReq = CreateObject("Microsoft.XMLHTTP")WinHttpReq.Open "GET", url, FalseWinHttpReq.sendIf WinHttpReq.Status = 200 ThenSet oStream = CreateObject("ADODB.Stream") oStream.OpenoStream.Type = 1oStream.Write WinHttpReq.responseBodyoStream.SaveToFile filePath,2  '1 = no overwrite, 2 = overwriteoStream.Close
End If
End If
End Sub
Function ExcelImportSet task = Client.GetImportTask("ImportExcel")        dbName = "\Archivos fuente.ILB\TASA_DOLAR_REFERENCIA_MC.xlsx"task.FileToImport = dbNametask.SheetToImport = "Diaria"task.OutputFilePrefix = "TASA_DOLAR_REFERENCIA_MC"task.FirstRowIsFieldName = "FALSE"task.EmptyNumericFieldAsZero = "FALSE"task.PerformTask  here error in initial loaddbName = task.OutputFilePath("Diaria")Set task = NothingClient.OpenDatabase(dbName)
End Function
' Eliminar campo
Function RemoveField
Set db = Client.OpenDatabase("TASA_DOLAR_REFERENCIA_MC-Diaria.IMD")
Set task = db.TableManagement
task.RemoveField "COL6"
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function
 

 

Brian Element Wed, 08/10/2022 - 10:01

Did you try importing the file directly into IDEA and then comparing the IDEAScript history with your script?  That might show where the problem is.

Also is the file being download completed and there are no error?  

ortizmario6025… Wed, 08/10/2022 - 14:02

yes, the file downloads fine, the problem is when the ExcelImportS import function goes, it gives me an error in the task.PerformTask command sequence that says initial download error.

and it's in the project directory

Brian Element Wed, 08/10/2022 - 14:53

So when you try and import it directly through IDEA you are getting an error?  Can you open it and resave it, it might not be in a "true" excel format and that is causing the error.

ortizmario6025… Thu, 08/11/2022 - 09:00

Exactly, when I download it manually I save it and import it manually with IDEA so it works for me. One question, how do I want to automate this process? Is it possible to execute a .bat from IDEA that downloads the file and saves it for me so that I can later invoke my macro? what solution could you have?