Posts by eureka18

eureka18 · Aug 15th 2019

got it to work:

changed the loop, especially .setcredentials:

Code

For x = 2 To lastrow
    For i = 0 To UBound(files)
        filedate = ThisWorkbook.Sheets(1).Cells(x, 1).Value
        full_file_path = filepath + filedate + "\" + files(i, 0) + filedate + files(i, 1)
           
        a = MkDir(filedate, filepath)
           
        fileurl = mainurl + filedate + "/" + files(i, 0) + filedate + files(i, 1)
        
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        
        'Then you have to GET direct file url
        WHTTP.Open "GET", fileurl, False
        WHTTP.SetCredentials "ad15qy", "Itsmyn80", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
        WHTTP.Send
        
        FileData = WHTTP.ResponseBody
        Set WHTTP = Nothing
        
        'Save the file
        FileNum = FreeFile
        Open full_file_path For Binary Access Write As #FileNum
            Put #FileNum, 1, FileData
        Close #FileNum
    Next i
Next x

Display More

eureka18 · Aug 15th 2019

Hi all,

I'm creating some vba to automatically download files from a password protected webserver. The issue is that it's not working for some reason. The script does not error out and it also creates files. but if i open the file it downloaded it says:

Quote

>This server could not verify that you
are authorized to access the document
requested. Either you supplied the wrong
credentials (e.g., bad password), or your
browser doesn't understand how to supply
the credentials required.

I'm sure that the credentials I put in are correct but for some reason it does not connect properly to the website. (if you try to access the website using a browser you get a popup to fill in login + pass). What am I misunderstanding on how to login on websites?[/QUOTE]

Code

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object


Dim lastrow As Long
Dim files(8, 1) As String
Dim filedate As String
Dim filepath As String




mainurl = "https://www-all.something.website.intranet/city/"
filepath = "C:\Home\My Documents\Webserver Files\"


files(0, 0) = "filename1"
files(0, 1) = ".txt"
files(1, 0) = "filename2"
files(1, 1) = ".txt.gz"
files(2, 0) = "filename3"
files(2, 1) = ".txt"
files(3, 0) = "filename4"
files(3, 1) = ".txt.gz"
files(4, 0) = "filename5"
files(4, 1) = ".txt.gz"
files(5, 0) = "filename6"
files(5, 1) = ".txt.gz"
files(6, 0) = "filename7"
files(6, 1) = ".txt.gz"
files(7, 0) = "filename8"
files(7, 1) = ".txt"


'userform to fill in credentials
Userform1.Show


'contains the dates from which to retreive the files
lastrow = ThisWorkbook.Sheets(1).Cells(Sheets(1).Rows.Count, 1).End(xlUp).Row


For x = 2 To lastrow


   For i = 1 To UBound(files)


      'date from which we need the files
     filedate = ThisWorkbook.Sheets(1).Cells(x, 1).Value


     'full path on computer to where the file needs to be saved
     full_file_path = filepath + filedate + "\" + files(i, 0) + filedate + files(i, 1)


     'check if folder exists, if not create it
     a = MkDir(filedate, filepath)


     'full url to file on server
     fileUrl = mainurl + filedate + "/" + files(i, 0) + filedate + files(i, 1)


     strAuthenticate = "start-url=%2F&user=" & login & "&password=" & Password & "&switch=Log+In"


     Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")


     'POST authentication string to the main website address not to the direct file address
     WHTTP.Open "POST", mainurl, False 'WHTTP.Open "POST", fileUrl, False
     WHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
     WHTTP.Send strAuthenticate


     'Then GET direct file url
     WHTTP.Open "GET", fileUrl, False
     WHTTP.Send


     FileData = WHTTP.ResponseBody
     Set WHTTP = Nothing


     'Save the file
     FileNum = FreeFile
     Open full_file_path For Binary Access Write As #FileNum
     Put #FileNum, 1, FileData
     Close #FileNum
   Next i
Next x


MsgBox "Files have been saved!", vbInformation, "Success"


End Sub

Display More

eureka18 · May 20th 2019

Hi, I'm working on a document template made by someone else but I'm getting a weird issue I do not understand.

Basically this person made a footer which is filled with information on document version (draft, finalised,etc) and the version number (version 1.0.1, etc). This data is sourced from a table within the document. The issue is however, that this footer is only filled for the first 2 pages of the document. After that it does not show. If I check in the footer than I do see that the field code is used but it just does not show a value. Tried refreshing and all. The last pasge where it does show is the page on which the table with the value is sourced.

Does anyone understand why this is happening and how to solve this?

Code

{STYLEREF "Doc Status" \* MERGEFORMAT} • {STYLEREF "Doc Label Version" \* MERGEFORMAT}{STYLEREF "Doc Version" \* MERGEFORMAT}

I also don't understand where/how the source of the Styleref is defined. I know I can find the style ref under Quick Parts -> Fields but it does not show where it get's the actual version of the document.

eureka18 · Oct 17th 2018

That seems to work perfectly thanks!

BELOW: Never mind, found the solution, I'm an idiot and forgot to redim the array.

Now I have one other question regarding arrays. Don't know if it should be in this topic, Please let me know if I need to move the question to a new topic.

So I multiplied the 2 arrays into ThroughputIn:

Code

For x = LBound(ThroughputOut) To UBound(ThroughputOut)
        For a = LBound(ThroughputOut(x), 1) To UBound(ThroughputOut(x), 1)
            For b = LBound(ThroughputOut(x), 2) To UBound(ThroughputOut(x), 2)
                ThroughputOut(x)(a, b) = ThroughputOut(x)(a, b) / 100 * DailyOutflow(a, b)
            Next b
        Next a
    Next x

I now want to sum the rows within each sub array. For this I use the following code:

Code

sub main_code()
Dim SumNCP(0 To 23) As Variant


For x = LBound(ThroughputIn) To UBound(ThroughputIn)
        For a = LBound(ThroughputIn(x), 1) To UBound(ThroughputIn(x), 1)
            SumNCP(x) = SumArray(ThroughputIn, x)
        Next a
    Next x
end sub




Public Function SumArray(Arr As Variant, x) As Double()
Dim strArray() As Double


For a = LBound(Arr(x), 1) To UBound(Arr(x), 1)
    strArray(a) = Application.Sum(Application.Index(Arr(x), a))
Next a


SumArray = strArray


End Function

Display More

I am getting a subscript out of range error in the public function on

Code

strArray(a) = Application.Sum(Application.Index(Arr(x), a))

the weird thing is that if I use the immediate window to see

Code

Application.Sum(Application.Index(Arr(x), a))

it gives me 1 value. But for some reason it will not be put into the

Code

strArray(a)

?

eureka18 · Oct 17th 2018

Quote from rory

I mean a basic loop construction like this:

Code

Dim x as long, y as long
for x = lbound(array1) to ubound(array1)
for y = lbound(array1, 2) to ubound(array1, 2)
array1(x, y) = array1(x, y) * array2(x, y)
next y
next x

I tried the following:

Code

For x = LBound(ThroughputOut) To UBound(ThroughputOut)
        For a = LBound(ThroughputOut(x), 1) To UBound(ThroughputOut(x), 1)
            For b = LBound(ThroughputOut(x), 2) To UBound(ThroughputOut(x), 2)
                ThroughputOut(x(a, b)) = ThroughputOut(x(a, b)) * DailyOutflow(a, b)
            Next b
        Next a
    Next x

but i get a type mismatch error on

Code

ThroughputOut(x(a, b)) = ThroughputOut(x(a, b)) * DailyOutflow(a, b)

eureka18 · Oct 16th 2018

How do you mean a loop?

I'm using the array approach because the output array will be manipulated a few times after as well.

eureka18 · Oct 16th 2018

I'm using the 2x2 as an an example. Real range is variable (depending on amount of days in month).

Example is 30x58 for one range. The array will contain 24 of such ranges.

Those 24 ranges should be multiplied with one other range which has the same dimensions (30x58)

eureka18 · Oct 16th 2018

Quote from rory

Other than seemingly ruling out matrix multiplication, that doesn't really clarify to be honest. Is ThroughputOut(0) supposed to be a 4 element array? If so, should it be 2*2 or some other alignment.

The elements should be 2x2 for both the Throughput array, as well as the range.

array{2x2, 2x2, 2x2} * range(2x2) = array_after_multiplication{2x2, 2x2, 2x2}

eureka18 · Oct 15th 2018

Quote from rory

What are you expecting as the output? Actual matrix multiplication, or just multipying each element by the corresponding element in the other array?

I would expect the outcome to be an array with 24 elements (0 to 23). every element of the array should have the multiplication of the cell values of the ranges.

So if you would take the example I described earlier. i would expect:

ThroughputOut(0) = throughput(0).A1 * range.A1, throughput(0).B1 * range.B1, throughput(0).A2 * range.A2, throughput(0).B2 * range.B2
ThroughputOut(1) = throughput(1).A4 * range.A1, throughput(1).B4 * range.B1, throughput(1).A5 * range.A2, throughput(1).B5 * range.B2

eureka18 · Oct 15th 2018

Hi,

I am working on a workbook in which I have to do some rather complicated formulas with arrays and ranges, it slowed excel down to the point that calculations took over half an hour. Hence, I wanted to do the calculations in vba.

I have an array, which I fill with ranges. so say:

Throughput Out = {A1:B2 ; A4:B5; A7:B8}

Now I want to multiply this with a range in another sheet (A1:B2). I'm getting a type mismatch when I try to multiply the ranges in the array with the range in the other sheet:

Code

ThroughputOut(x) = ThroughputOut(x) * DailyOutflow

Below my full code:

Code

'Create Arrays
Dim ThroughputOut(0 To 23) As Variant
Dim ThroughputIn(0 To 23) As Variant


Dim AmountPaid(0 To 23) As Variant
Dim AmountReceived(0 To 23) As Variant


'Create Ranges
Dim DailyOutflow As Range
Dim DailyInflow As Range


Dim LastCol As Long


'Set Scenario Names
Call SetScenarios




'Loop through scenarios
For Each Item In Scenarios()
    Set WbScenarios = Workbooks.Open(ActiveWorkbook.Path & "\" & Item & ".xlsx")




    'Set worksheets
    Call SetSheets
    
    'Set Amount of Days in Month
    Call CountDays




    'Find last column filled with currency
    LastCol = ShtPayments.Cells( _
                Application.Match( _
                    StrPaymentsOut, _
                        ShtPayments.Range("b:b"), 0), ShtPayments.Columns.Count).End(xlToLeft).Column
    
    'Fill Ranges with Source Data for each scenario
    For x = 0 To 23
    
        ThroughputOut(x) = ShtThroughputOut.Range( _
                            ShtThroughputOut.Cells( _
                                Application.Match( _
                                    StrThroughput & Format(x, "00") & ":00  (CCY timeframe)", _
                                    ShtThroughputOut.Range("b:b"), 0) + 2, 3), _
                            ShtThroughputOut.Cells( _
                                Application.Match( _
                                    StrThroughput & Format(x, "00") & ":00  (CCY timeframe)", _
                                    ShtThroughputOut.Range("b:b"), 0) + 1 + DaysInMonth, LastCol))
    
        ThroughputIn(x) = ShtThroughputIn.Range( _
                            ShtThroughputIn.Cells( _
                                Application.Match( _
                                    StrThroughput & Format(x, "00") & ":00  (CCY timeframe)", _
                                    ShtThroughputIn.Range("b:b"), 0) + 2, 3), _
                            ShtThroughputIn.Cells( _
                                Application.Match( _
                                    StrThroughput & Format(x, "00") & ":00  (CCY timeframe)", _
                                    ShtThroughputOut.Range("b:b"), 0) + 1 + DaysInMonth, LastCol))
    
    Next x




    Set DailyOutflow = ShtPayments.Range( _
                            ShtPayments.Cells( _
                                Application.Match( _
                                    StrPaymentsOut, _
                                    ShtPayments.Range("b:b"), 0) + 2, 3), _
                            ShtPayments.Cells( _
                                Application.Match( _
                                    StrPaymentsOut, _
                                    ShtPayments.Range("b:b"), 0) + 1 + DaysInMonth, LastCol))
                                    
    Set DailyInflow = ShtPayments.Range( _
                            ShtPayments.Cells( _
                                Application.Match( _
                                    StrPaymentsIn, _
                                    ShtPayments.Range("b:b"), 0) + 2, 3), _
                            ShtPayments.Cells( _
                                Application.Match( _
                                    StrPaymentsIn, _
                                    ShtPayments.Range("b:b"), 0) + 1 + DaysInMonth, LastCol))


      
    'multiply Throughput Out with Daily Outflow and Throughput In with Daily Inflow
    For x = 0 To 23
              
        ThroughputOut(x) = ThroughputOut(x) * DailyOutflow
        ThroughputOut(x) = ThroughputIn(x) * DailyInflow
    Next x
    
    
    
    
    WbScenarios.Close
Next Item

Display More

eureka18 · Oct 15th 2018

Unfortunate, I adjusted my code to make a loop in opening the files and setting that as the workbook. Thanks for the info!

eureka18 · Oct 15th 2018

Hi yes, that one seemed to work.

Working on mulitple projects here with different issues, to keep the posts ontopic i decided to make multiple threads.

eureka18 · Oct 15th 2018

That did the trick? I just don't understand how that sumproduct function works? it sums 2 arrays, one in which only things >0 are, and the other one <=85. But then how does it come to that output? the sumproduct is still an array right?

eureka18 · Oct 15th 2018

Hi,

I want to set references to a workbook without directly opening it (i want to put the workbooks in an array and loop through it later in the code)

I know workbooks.open(address) can be used but that directly opens it... Is there a way in which you do not open the workbook but only set the variable? Below code gives "subscript out of range" on Set NPCS1

Code

Public Scenarios(1 To 4) As Variant


Sub SetWorkbooks()
Set NPCS1 = Workbooks.(ActiveWorkbook.Path & "\" & name1 & ".xlsx")
Set NPCS2 = Workbooks.(ActiveWorkbook.Path & "\" & name2 & ".xlsx")
Set NPCS3 = Workbooks.(ActiveWorkbook.Path & "\" & name3 & ".xlsx")
Set NPCS4 = Workbooks.(ActiveWorkbook.Path & "\" & name4 & ".xlsx")


Array(1) = NPCS1
Array(2) = NPCS2
Array(3) = NPCS3
Array(4) = NPCS4


for each item in Array()
  item.open
  DoSomehting
  item.close
next item


end sub

Display More

eureka18 · Oct 11th 2018

Quote from NBVC

The only way we can say for sure is if you post the workbook so we can see where you've gone wrong.

One thing to check is make sure that B5 contains a numeric value and that it is not formatted as text or is blank.

What do you mean with cell B5? You mean "<85"? 85 should just mean smaller than 85

added an example of the file with formula in yellow cell on sheet named Example

eureka18 · Oct 11th 2018

Hi,

I'm working on a formula which has to count which values in a range are smaller than 85. However, it always returns 0 and I can't seem to find out why.

Code

=COUNTIF(INDEX(IFERROR(VALUE(OFFSET('[MR Data.xlsx]Throughput OUT'!$C$1:$BW$31,MATCH("Throughput at  "&J16&"  (time)",'[MR Data.xlsx]Throughput OUT'!$B:$B,0)+1,0)),0),,MATCH($B16,'[MR Data.xlsx]Throughput OUT'!$C$12:$BW$12,0)),"<85")

The range that I get is:

Code

{0;0;99.2003106902189;99.3237176764998;98.1927164523149;98.5735689912153;99.392772040846;0;0;98.6325134692947;98.7672864746517;97.6097208803074;99.3127577005428;99.5343545290336;0;0;91.8404980728644;98.9542351167087;97.764265176103;97.2293725600979;98.7022097400667;0;0;98.5996310616598;99.4744845394647;83.1670428689839;97.8408794715537;98.8089104637371;0;0;0}

So there should be values which are smaller than 85. Why do I get 0 results?

eureka18 · Feb 27th 2018

yep that worked perfectly! thanks!

eureka18 · Feb 26th 2018

Hi people,

I've been crunching my head for a while now on this issue i have. I have a formula with an array of numbers. From this array I want to get the least negative number (the negative number closest to 0). I am using the following formula:

Code

=MAX({-668;-576;-484;-393;-303;-211;-120;-29;62;153;244;335;426;517;609;700;791;885;977}<0)

For some reason excel gives me 0 as the outcome of this. What am I doing wrong?

eureka18 · Jan 23rd 2018

Hi,

I have a userform which opens when the workbook is opened.

Code

Private Sub Workbook_Open()
UserForm1.Show
End Sub

However, when I open the workbook, it also opens the VBA editor in front of the userform and it highlights Userform1.show in yellow. Only after I minimise the vba editor, I can see the userform. How can I disable the VBA editor from popping up?

Thx!

eureka18 · Jan 22nd 2018

yes:

Code

Public ImportWB As Workbook

and

Code

Set ImportWB = Workbooks.Open(StrPath & "\" & Item)

I found out that the following code works

Code

ImportWB.Activate
ImportWB.Sheets(YCSht).Range(Cells(y, ExcelYCPlanningColumn), Cells(y, wColNum(ExcelYCPlanningColumn) + Horizon)).Copy
ThisWorkbook.Activate
ThisWorkbook.Sheets(YCSht).Cells(x, ExcelYCPlanningColumn).PasteSpecial xlPasteValues

However, this should be possible in 1 row right?

Posts by eureka18

Downloading file using vba

Downloading file using vba

Field value not showing

multiplying ranges in array

multiplying ranges in array

multiplying ranges in array

multiplying ranges in array

multiplying ranges in array

multiplying ranges in array

multiplying ranges in array

set workbook without directly opening it

set workbook without directly opening it

Countif not working

set workbook without directly opening it

Countif not working

Countif not working

Find least negative value in array

Find least negative value in array

VBA editor opens automatically

Copy Range from Other Workbook to Activeworkbook Application/Object Error