Posts by jstanley81

    Hello everyone,


    I'm very new to writing scripts but I have a script written that logs into a website, enters in the parameters needs to pull a report. The website then generates the report into Excel. My issue is that I can't figure out how to make the script use the Save As prompt so that I can specify where to store the downloaded file. Suggestions?

    I've recorded a macro and to do a pivot table (and made some adjustments to it so it would work every time). The pivot table works great except that both the column and row have a (blank) as the last field. This doesn't make sense because there is no blank cells that it captures. If I manually produce the pivot table, the pivot shows no blanks. This only happens with the macro run. I've stepped through the code to see the data it is capturing and have confirmed there is no blank cells. I'm at a loss. Normally I wouldn't care too much about this except that it takes the data and inputs it into a report and then formats it. The blanks are throwing off the formatting. Any suggestions as to why this is happening?

    Re: Copy sheet and macros to same workbook


    EDIT: I found a solution. Not sure if it's the best way or not but I did an If/ElseIF statement to look at the active sheets name first and then based on that run the macro. Tested and it does exactly what I need it to now.

    I have a macro that copies a sheet to the same workbook which is exactly what I need it to do. However, the copy also has macros coming with it which I want but I need those copied macros to reference the new sheet. Hope that made sense. Anyways, the macros on the new sheet is reference the the original sheet. Anyone know how to make the macros reference the newly created sheet?

    Re: Counting cells that are blank and not equal to 0


    Figured out another method. I did this instead...


    Code
    1. For i = 2 To lastRow
    2. If IsEmpty(lotusRawData.Cells(i, 5)) = False Then
    3. x = x + 1
    4. End If
    5. Next i
    6. y = lastRow - x - 1


    I had to use the minus 1 after the x because for some reason the headers were causing an issue in the count. Now I'm getting the right answer.

    I cannot figure out how to make this count right. I'm looking for a count of true blank cells. Here is the code.


    Code
    1. For i = 2 To lastRow
    2. If IsEmpty(lotusRawData.Cells(i, 5)) = True Then
    3. If lotusRawData.Cells(i, 5).Value <> 0 Then
    4. x = x + 1
    5. End If
    6. End If
    7. Next i


    I know the count should be 57 but it's returning 2212 because it's also including the 0's.

    Re: Status message


    Ok so I'm reviewing the code you have in the progress message. Please tell me if I'm understanding this correctly. On the line that says "'// Do something in the background
    ActiveSheet.Rows(lngLoop).EntireRow.Select", I would call my subroutines? So it would read.... Call jan, Call feb, Call mar and so on? Then I can make the text say whatever I need.

    Re: Status message


    Not sure where I'd be without you in this project right now! Thanks! Going straight to the developer tab and running from there allowed me to demo. Now I have a better understanding of this. I really appreciate your help! I'm so ready to be done with this project. As much as I really do love coding and seeing the final product....I'm beyond ready to put the project to rest and be done with it.

    I'm finished writing my "analysis app" in excel. It works amazingly thanks to the help everyone here gave when I was getting stuck so thank you to everyone that helped me out! I would like to add one final thing to this. It takes a little bit for the calculations to complete which is expected considering the amount of data it is sifting through. I would like a msgbox to appear when the report is running to show which month it's currently calculating. It runs from Jan - Dec and each month is in its own sub routine so it goes "Call Jan" and then "Call Feb" and so on. I want the msgbox to show "Calculating Jan" and once done show Calculating Feb and so on with 3 periods that appear one at a time then disappear and then appear again if that makes sense. I've done this once before a long time ago but don't remember how I did it. I can't even remember where to begin. I did some googling on it but was left even more confused. I understand that it requires the use of another userform but that's as far I understand it. I think maybe something like .visible and some sort of timer. I know I'm on the right track but am so lost at the same time. Anyone have any ideas on how to accomplish this or maybe already have code that does it that I can just change the verbiage around?

    I've written a VBScript that is automatically logging me into a site. This works fine. Once logged in, a new window pops open (please note that it is a new window and not a tab. I need to click a link in the newly opened window to get to the next section of the site. I've searched and searched can't get a straight answer on how to accomplish this. I am VERY new to this type of programming. Here is the source code directly from the site in which the button is.


    HTML
    1. <li title="AdvancedInsight" class="sprite-adhocreports" id="mnuAdHocReporting" onmouseover="oToolbar.onMouseOver(this)" onmouseout="oToolbar.onMouseOut(this)" onmousedown="oToolbar.onMouseDown(this)" onmouseup="oToolbar.onMouseUp(this)" onclick="mainMenu.open(this)" data-itabindex="" data-url="reports/adhoc.htm?adhoctype=reports" data-resizable="1" data-wintop="0" data-winheight="720" data-winwidth="1016" data-mode="window" data-frameid="" data-menuitem="">

    Not sure how to combat this. We have 2 separate sheets within a workbook. We need to be able to do a vlookup by name. Sounds simple enough but the problem is that one sheet will have the persons First Name then Last name. The second one will have the same except some the middle initial which throws off the vlookup since they don't match. Is there any way to quickly remove the middle initial or even a formula that wouldn't "care" about that middle initial but still do the vlookup?

    Re: Save path issue


    Nice catch but that still didn't resolve it. The error I get says "Run-time error '1004': Method SaveAs of object_Workbook failed.

    Re: Save path issue


    Forgot to post the actual code. Yeah, looks to be one of those mornings. So I've just changed the file path to have quotations around everything and still no luck. When I go to debug, I can see that FPath is reading correctly. I also verified that the path does actually exist. (File path was just changed to the network instead of on the C: drive.)


    Code
    1. FName = cboIssueSelect.Value & " - " & cboMonthSelect.Value
    2. FPath = "\" & "\" & "rcsfs" & "\" & "Analysis Files" & "\" & "Billing"
    3. Set NewBook = Workbooks.Add
    4. report.Copy After:=NewBook.Sheets(1)
    5. NewBook.Sheets(1).Delete
    6. report.Delete
    7. NewBook.SaveAs FPath & FName, FileFormat:=52

    I've searched and searched and none of the solutions I've found seem to work. The save path I need to use in my project has a space in it. Searching through several threads, I've seen suggestions of using double quotes around the file path. Tried that and it still threw an error. Also saw to use chr(34). Tried that and still get an error. Here is what I've tried....


    FPath = "C:\Analysis Files\Billing"
    FPath = ""C:\Analysis Files\Billing"
    FPath = chr(34) & "C:\Analysis Files\Billing" & chr(34)


    I'm at a loss here.

    I may be confused by this and just need to code this a little differently but I have Application.DisplayAlerts set to False in my code. However, when my code reaches the point of opening a new workbook, the new workbook prompts to ask if I want to update the workbook with the links that are attached to it. I assumed Application.DisplayAlerts would resolve this but it didn't. Is there something else I should be using? By default, I would like it to "click" do not update. Seems like it's something fairly simple but never had to deal with that particular prompt before.