• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA MACRO I need sql query

trprasad78 said:
Hi @p45cal Hope you doing good.
at any chance it will complete tomorrow(Monday)?
sorry to push you, if you able to finish please let me know.

Thank you.
Click to expand...
I'm not getting alerts from Chandoo at the moment and I can't log in (always get 'Internal server Error ') on a PC, so I'm replying on a smart phone.
Prompted by your wanting to do this with Sql, I've been teaching myself intermediate Sql and am slowly getting somewhere, but no, you won't get an Sql answer from me in the short term.
I will continue to try and get an Sql solution.

I will try to put together a VBA solution, if only to have something to compare my results with.
 
p45cal said:
I'm not getting alerts from Chandoo at the moment and I can't log in (always get 'Internal server Error ') on a PC, so I'm replying on a smart phone.
Prompted by your wanting to do this with Sql, I've been teaching myself intermediate Sql and am slowly getting somewhere, but no, you won't get an Sql answer from me in the short term.
I will continue to try and get an Sql solution.

I will try to put together a VBA solution, if only to have something to compare my results with.
Click to expand...
If you find any alternative solution also please let me know. How about Ms access ... i just trying that too.
 
Chihiro said:
It can be done in VBA. Just that SQL query isn't well suited for something like this. As it will at least require multiple select and nested queries.

You can, use Scripting.Dictionary and other methods to bring in the data, but then I'm still confused about your criteria and what will be your final output.

It may be easier to set up formula/flag in source file and query based on that flag column.
Click to expand...

I am not good in programme, I had tried in Ms access but not got.

Even if you have any alternative way to get that please help me.

MasterFile.xlsx refer to master file, I need employee wise for given period.
LOP

LOP = > UNPAID-ABS + if unpaid leave fall in before and after WEEKOFF-OF , then WEEKOFF-OF also consider as LOP

I need out put with Createdby (employee) and Total no. of hours LOP>
If i get out put in EXCEL i can use that for vba or MS access.
 
Attached is an interim solution which has a few riders; I'm not at my usual computer and have developed this with Exel2003 on a Windows XP machine, so there are several things which may not work:
1. This is a .xls file, not .xlsx (you should have no trouble opening it)
2. In the VBE there is a reference set to Microsoft ActiveX Data Objects 2.7 Library. This may be missing from your machine in which case choose another version of the same reference. It might work.
3. You will probably have to save your Copy of MasterFile.xlsx as Copy of MasterFile.xls (that is, as an Excel 97 to 2003 file, or something like that)
4. You might be able to avoid points 2 & 3 above if you change the connection string in the code (it's the line beginning sConnect =) to something more like:
Code: 
sConnect = "ODBC;DBQ=C:\Users\zzzzzz\Documents\Copy of MasterFile.xlsx;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
5. Don't forget to update the file and path name of the MasterFile in the code (same line).

There are two buttons on the sheet, the only difference being how the start and end dates are obtained.

The solution is not a SQL-only solution; 3 SQL queries are used in the code and the resulting recordsets are used to create an array which is placed on the worksheet.
The solution only gives Emp Names where there are LOP days. Any names not appearing means they have no LOP days.

How it's supposed to work:
Get list of names to process (SQL)
For each name:
Get list of Dates and Hours where:WEEKOFF-OF,Approved,Hours>=8 for the day, and nothing else on that date (SQL)
Get list of Dates and Hours where:UNPAID-ABS,Approved,Hours>=8 for the day, and nothing else on that date (SQL)
Run through the WEEKOFF-OF dates looking for runs of sequential dates, and when one is found check that there are UNPAID-ABS on the day before and the day after that run (a run can be only one day long), and if there are, count those WEEKOFF-OF days as LOP, but only those dates between start and end dates. These data are put into an array.
When the names have all been processed, the array is written to the sheet.

I intend to re-write for Excel 2010-2016 when I get to a machine with it on. That may not be for a week or three.

Anyway, something for you to play with.
 

Attachments

  • Chandoo35485Output_b.xls
    62 KB · Views: 5
p45cal said:
Attached is an interim solution which has a few riders; I'm not at my usual computer and have developed this with Exel2003 on a Windows XP machine, so there are several things which may not work:
1. This is a .xls file, not .xlsx (you should have no trouble opening it)
2. In the VBE there is a reference set to Microsoft ActiveX Data Objects 2.7 Library. This may be missing from your machine in which case choose another version of the same reference. It might work.
3. You will probably have to save your Copy of MasterFile.xlsx as Copy of MasterFile.xls (that is, as an Excel 97 to 2003 file, or something like that)
4. You might be able to avoid points 2 & 3 above if you change the connection string in the code (it's the line beginning sConnect =) to something more like:
Code: 
sConnect = "ODBC;DBQ=C:\Users\zzzzzz\Documents\Copy of MasterFile.xlsx;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
5. Don't forget to update the file and path name of the MasterFile in the code (same line).

There are two buttons on the sheet, the only difference being how the start and end dates are obtained.

The solution is not a SQL-only solution; 3 SQL queries are used in the code and the resulting recordsets are used to create an array which is placed on the worksheet.
The solution only gives Emp Names where there are LOP days. Any names not appearing means they have no LOP days.

How it's supposed to work:
Get list of names to process (SQL)
For each name:
Get list of Dates and Hours where:WEEKOFF-OF,Approved,Hours>=8 for the day, and nothing else on that date (SQL)
Get list of Dates and Hours where:UNPAID-ABS,Approved,Hours>=8 for the day, and nothing else on that date (SQL)
Run through the WEEKOFF-OF dates looking for runs of sequential dates, and when one is found check that there are UNPAID-ABS on the day before and the day after that run (a run can be only one day long), and if there are, count those WEEKOFF-OF days as LOP, but only those dates between start and end dates. These data are put into an array.
When the names have all been processed, the array is written to the sheet.

I intend to re-write for Excel 2010-2016 when I get to a machine with it on. That may not be for a week or three.

Anyway, something for you to play with.
Click to expand...
Thank you so much for your support.

I am getting error while running

error Run-time error '-2147467259 (80004005)':
[MICROSOFT][ODBC DRIVER MANAGER] Data source name not found and no default driver specified


I changed the file path and file name .xls
I need to create any connection in odbc connection ?
Data Sources (ODBC)
if so please share the steps too.

Thank you
 
@p45cal Thank you so much its working great.
same query i can run in MS access ? if i keep the Master excel file as Ms access Table and run the query/Macro in access it show on screen ?

I will also check that...

Thank you :)
 
You must log in or register to reply here.
Back
Top