downloading an excel file from a page

10 posts / 0 new
Last post
ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54
downloading an excel file from a page

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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@...
Offline
Joined: 09/03/2021 - 11:54

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

ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

I will do what you tell me before

ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

exactly as you say gives error

ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

apply the command manually and as you want it gives the error

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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@...
Offline
Joined: 09/03/2021 - 11:54

Hello. I will try and tell you

ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

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?

ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

or make a separate macro that does a SaveAs FileName?, but I don't know how to start