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?

Quote from Carim

Sorry for misunderstanding ... :wink:

Thought you needed a VBA solution ...

Since you need a worksheet formula ...

You have to be 100 % sure your Column A is filled with Text ... NOT Actual Dates ...

if so ... you can test following formula:

Code

=Indirect("'[MRR Operational Reporting V1_"&A2&".xlsx]Goal Worksheet'!$X$6")

If you happen to have actual dates in Column A ... an additional manipulation will be required ...

Hope this will help

Display More

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

Quote from Carim

Sorry to disappoint you ...

the proposed expression you are looking for

Code

Cstr(Cells(i,1).Value)

is ... VBA ... :wink:

I know, I meant that I need it in Sheet Formula... not VBA

Quote from Carim

Thanks for the clarification...

The only remaining point is the exact format of your data in Column A ...

If you have actual dates, you would need to convert these numbers back to strings ...

Code

='[MRR Operational Reporting V1_" & Cstr(Cells(i,1).Value) & ".xlsx]Goal Worksheet'!$X$6

Hope this will help

Display More

This is a sheet formula, not VBA

Quote from Carim

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...

[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...

[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:

='[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?

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:

NewBook as Object
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:

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?

There are about 14 files in the folder, it calls the 1st one and after that myFile = ""

myPath = "G:\Path\"
myExtension = "*.xls*"
MyFile = Dir(myPath & vbDirectory)
    Do While MyFile <> ""
        If MyFile <> "." And MyFile <> ".." Then
        'do stuff
        End If
    myFile = Dir
    Loop

Any ideas?

Re: Excel connection query error

yes, but ive found that Microsoft products can be strange. I don't question it anymore, just try to solve one problem at a time ha.

Re: Excel connection query error

Actually, just solved it. The correct format is:

SELECT * FROM ( [TABLE1] a
LEFT JOIN [TABLE2] b ON a.[Fieldname] = b.[Fieldname] )
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:

SELECT 
*
FROM  `Path.accdb`.ddqAnswers ddq
LEFT JOIN `Path.accdb`.tblDdqSwiftMessaging swft
ON ddq.Partner = swft.PartnerID

This works fine.

This also works fine:

SELECT 
*
FROM  `Path.accdb`.ddqAnswers ddq
LEFT JOIN `Path.accdb`.tblDdqCutOffTime cut
ON ddq.Partner = cut.PartnerID

However, this creates an error:

SELECT 
*
FROM  `Path.accdb`.ddqAnswers ddq
LEFT JOIN `Path.accdb`.tblDdqCutOffTime cut
ON ddq.Partner = cut.PartnerID
LEFT JOIN `Path.accdb`.tblDdqSwiftMessaging swft
ON ddq.Partner = swft.PartnerID

Anyone know why?

I need a vba code that can:

1. create a connection to an access database
3. creates the sql string using my variables
4. exuctes that sql statement

Anyone know how to do this or have a link?