Posts by xlbrian

• How to perform a Linear Regression

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.

• unable to "get data" with excel

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?

• Variable Cell Reference based on Path

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

• Variable Cell Reference based on Path

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

• Variable Cell Reference based on Path

This is a sheet formula, not VBA

• Variable Cell Reference based on Path

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

• Variable Cell Reference based on Path

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

• Create invisible workbook

Code
``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).

• Create invisible workbook

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

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.

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

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
``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?

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

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

Code
``````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?

• Excel connection query error

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.

• Excel connection query error

Re: Excel connection query error

Actually, just solved it. The correct format is:

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

• Excel connection query error

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.

• Excel connection query error

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

• Excel connection query error

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

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

This works fine.

This also works fine:

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

However, this creates an error:

SQL
``````SELECT
*
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?

• VBA to import data into Access database

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?