Posts by xlbrian

    Hey guys, I did this in college but its been awhile. I have 5 columns of baseball attributes (Power, Contact etc on a 20-80 scale) and an output (WAR, which is between -1.5 and 11). Sample size of 753 players.


    I need to be able to create a formula using the 5 attributes to predict a players WAR. How is this done? I have already added in the analysis toolpak, just not sure which test to perform and how to set it up. Assume the Columns are in the B-F columns and WAR is in G column.

    I just got a new computer and have a Microsoft office subscription, but when I try to "Get Data", no matter what I pick, nothing happens. If I click "From Web" for example, I push the button but nothing happens. Any ideas?


    It didn't work because the workbooks are all closed... after doing more research, I think I'm up the creek without means of transportation


    This is a sheet formula, not VBA

    Hello,


    Not sure to fully understand your question ...


    May be Indirect() ...


    Ok, so I'm going to give a formula that doesn't work just so you understand what I'm TRYING to accomplish:



    I want to turn the date portion of the path... which is 05092018 in the below formula...


    Code
    1. [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]='[MRR Operational Reporting V1_05092018.xlsx]Goal Worksheet'!$X$6[/SIZE][/FONT][/COLOR][/align]


    into a variable based on the A column like this...


    Code
    1. [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]='[MRR Operational Reporting V1_" & A2 & ".xlsx]Goal Worksheet'!$X$6[/SIZE][/FONT][/COLOR][/align]


    again, the way I inserted the variable doesn't work... it was just to show you what I'm trying to do.

    I need to pull values from about 80 excel files that are all in the same folder and named the exact same thing except for a datefield. Luckily, the value I need in each workbook is also in the exact same Cell.


    So I am hoping that I can quickly pull all the data I need using this Formula:

    Code
    1. ='[MRR Operational Reporting V1_05092018.xlsx]Goal Worksheet'!$X$6


    except the date 05092018 is different for each file.


    I have the date parsed out this way in the A column (A2 = 02072018, A3 = 02082018, A4 = 02092018, etc).


    What would the formula be so that I can use the A Column cells as a variable in the above formula?

    Code
    1. ActiveWindow.Visible = False


    This successfully hides the workbook... but it does it after the workbook has been created, not at the same time. The goal was for the workbook to be created in the background without ever making an appearance.


    Generally when the workbook makes an appearance, it creates an opportunity for the user to cause an error (for instance, if the user is typing in a word document, and the excel workbook screen pops up, this sometimes causes the words to be typed onto the spreadsheet instead of the word document).

    I have a code that builds about 200 workbooks based on a list that is on the parent Workbook. Because this takes about 30 minutes, I want the user to be able to continue to do work (with all code activity done in the background).
    Making the Parent workbook invisible was easy, but I am unable to find how to create new workbooks without them popping up as visible. Here is the current code snippet:


    Code
    1. NewBook as Object
    2. Set NewBook = Workbooks.Add


    I do not seem to have the option to tell it to create the workbook as Visible = False. I can hide it after its created but that does not accomplish the goal.

    Re: looping thorugh directory but it calls 1st file and no more


    Ok, I figured out the problem. I want to go through each file and create a file somewhere else if it doesn't already exist. Within the loop i'm checking if the file exists with this code:



    Code
    1. If Dir(curPath & curFile & curExt) = "" Then


    From what I read, this is what is screwing up the loop. What options do I have since this method is ruining the DoWhile Loop?

    Re: Excel connection query error


    Actually, just solved it. The correct format is:



    SQL
    1. SELECT * FROM ( [TABLE1] a
    2. LEFT JOIN [TABLE2] b ON a.[Fieldname] = b.[Fieldname] )
    3. LEFT JOIN [TABLE3] c ON a.[Fieldname] = c.[Fieldname]


    I'm assuming that for each additional JOIN, you would need a new set of parenthesis so that you only ever have on join outside of the parenthesis, however I have not tested yet.

    Re: Excel connection query error


    I came across this problem again... it seems as long as I do ONE join, it works fine. As soon as I add a second join, I get an error. I would think that the syntax would be the same regardless of how many joins you use.

    Re: Excel connection query error


    good point

    Quote


    [microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ddq.Partner = cut.PartnerID LEFT JOIN 'Path.accdb'.tblDdqSwiftMessaging swft ON cut.PartnerID = swft.PartnerI'.

    In the connection properties I have the following entered into the Command Text:


    SQL
    1. SELECT
    2. *
    3. FROM `Path.accdb`.ddqAnswers ddq
    4. LEFT JOIN `Path.accdb`.tblDdqSwiftMessaging swft
    5. ON ddq.Partner = swft.PartnerID


    This works fine.


    This also works fine:


    SQL
    1. SELECT
    2. *
    3. FROM `Path.accdb`.ddqAnswers ddq
    4. LEFT JOIN `Path.accdb`.tblDdqCutOffTime cut
    5. ON ddq.Partner = cut.PartnerID


    However, this creates an error:


    SQL
    1. SELECT
    2. *
    3. FROM `Path.accdb`.ddqAnswers ddq
    4. LEFT JOIN `Path.accdb`.tblDdqCutOffTime cut
    5. ON ddq.Partner = cut.PartnerID
    6. LEFT JOIN `Path.accdb`.tblDdqSwiftMessaging swft
    7. ON ddq.Partner = swft.PartnerID


    Anyone know why?