# Posts by Johnnyv015

• ## Workdays in the Month formula? [SOLVED]

I figured it out!!!

I was looking to use this formula to figure actual averages across TONS of 0's/ blanks in the data set.

I was able to use a SUMIFS formula with this formula to divide the sum of the date range by days worked.

I was able to use the following to achieve this:

:oops:

Quote

:oops:

this generates my count of working days for the month, "holidays" references a list I created of Holidays scheduled closed in the office.

Once I generated a count of days in each month I was able to vlookup this for my number to divide by.

Full formula below:

Quote

=SUMIFS('Sheet1'!\$H:\$H,'Sheet1'!\$C:\$C,Sheet2!\$A2,'Sheet1'!\$U:\$U,Sheet2!\$J\$1)/VLOOKUP(\$V\$1,\$AF:\$AG,2,FALSE)

Vlookup references the results from the first formula.

Can't believe this took me the time it did... :thanx:

• ## Workdays in the Month formula? [SOLVED]

Hello, looking for a formula that will generate a count of the workdays (Monday - Friday) without holidays for any given month of any year.

Anyone have any suggestion on a simple way of achieving this task?

I would also like to run this out for future months/years so flexibility would be nice.

Thanks!

• ## Saving Excel File as Tab Delimited Text

Hello,

Just following up on this, hope someone can push me in the right direction.

• ## Saving Excel File as Tab Delimited Text

Hello,

Thanks for the response!

I have been validating the save information for the file path using a msgbox. I can confirm that it is using the correct naming convention.

However I am still seeing the same problem.

My company has recently converted to "One Drive" linked drives for all systems, could this be the cause for the errors I am seeing?

If so does anyone have experience with these and have a workaround for saving files to a sharepoint linked drive?

John

• ## Saving Excel File as Tab Delimited Text

Howdy all,

I have put a code together I am trying to use that Updates a spreadsheet then saves as a Tab Delimited Text file with a filename based off a cell refrence as well as the date updated.

Code is as follows:

I am able to run through the entire code without issues until I hit the save. This flags with a 1004 error every run.

Thanks for the help! You guys are the REAL MVPs!!!!!
:cheers:

• ## Issues Saving to PDF using VBA

Hello, I have an excel file I am using to update a pdf everytime the excel file is closed. However I am running in to the issue where other users may be accessing this pdf across the shared network giving me an error on attempting to save the updates. Is there any way to force close the pdf prior to saving? Or maybe set a time limit for viewing the pdf?

Not sure how to approach this issue. Any help is greatly appreciated.

Thanks!

• ## Pulling Innertext from Table using VBA

Hello,

I have been working on a project for a few days and have come to a road block. I am trying to pull data from a table in IE into my workbook using VBA.

The code I have currently logs into our system, navigates through a few links and drop downs. enters a date range and gets the report. Now I am just having problems pulling the data from this report into excel.

Here is the HTML code for the table I am working with.

Code
2. <script type="text/javascript">function ButtonHandlerOnEnterKey(button, objEvent){ if(objEvent.getKey() == 13) { button.handler.call(button.scope); }}Ext.onReady(function(){ favStore = new Ext.data.JsonStore({autoLoad:true, fields:[{name:'qID', type:'int'}, 'qName'], proxy:{type:'ajax', url:'jsonfavoritelist', reader:{root:'favoriveList', type:'json'}} }); var cmbFav = Ext.create('Ext.form.field.ComboBox', {id:'cmbFav', store:favStore, valueField:'qID', displayField:'qName', typeAhead:true, forceSelection:true, queryMode:'local', width:200, renderTo:'tdFav', listeners:{select:function(cb, rec, ind){document.location = 'queryparams?QueryID=' + cb.getValue();}} });});</script>

You will see the data needed in the TD lines of code. All I need is the number amounts.

Thanks,
John

• ## Using Sendkeys in IE. VBA code

Re: Using Sendkeys in IE. VBA code

Any one have any ideas on this one? Still stuck.

• ## Select from a drop down menu using VBA

Re: Select from a drop down menu using VBA

Thanks for the help, But I have solved this problem. for future reference the code is listed below.

Code
1. Set oSelect = MyBrowser.document.getElementById("CatID")
2. oSelect.Focus
3. oSelect.selectedIndex = 6
4. oSelect.FireEvent "onchange"

this selects the 6th option from the top in this drop box.

• ## Using Sendkeys in IE. VBA code

Hello, I am working on a VBA code that will navigate through several links then enter a date range and run a report.

As of now I have everything working except the Sendkeys function for entering the date range.

As you can see in the code, on the last page I am prompted with 2 calendars for selecting the dates .

The dates selected will ALWAYS be yesterday.

When navigating through this via mouse, you simply click the calendar and press the left arrow then enter. This will input yesterdays date.

Now for some reason my sendkeys line is not executing in IE.

Is there anything I should add to this as to link it to IE or the calendar?

Any push in the right direction would be a great help.

Also this is the HTML code for the calendar boxes on the page.

So my problem here, is I need the SendKeys function to Act in the calendar box that pops up when "ext-gen1054" and "ext-gen1056" are selected.

Thanks,
John

• ## Using Sendkeys in IE. VBA code

Hello, I am working on a VBA code that will navigate through several links then enter a date range and run a report.

As of now I have everything working except the Sendkeys function for entering the date range.

As you can see in the code, on the last page I am prompted with 2 calendars for selecting the dates .

The dates selected will ALWAYS be yesterday.

When navigating through this via mouse, you simply click the calendar and press the left arrow then enter. This will input yesterdays date.

Now for some reason my sendkeys line is not executing in IE.

Is there anything I should add to this as to link it to IE or the calendar?

Any push in the right direction would be a great help.

Also this is the HTML code for the calendar boxes on the page.

Thanks,
John

• ## Select from a drop down menu using VBA

Hello, I am trying to click on a specific option in a drop down on an IE page and I am having trouble with this code. As of now this is the code I have to access the page. Dim

Now I need to click on a drop down and select an option from a list(in the same window.) Here is the HTML for the Dropdown box.

HTML
1. <SELECT onchange=document.frmMain.submit(); id=CatID class=Entry name=CatID> <OPTION selected value="0">-Select-</OPTION> <OPTION value="42">Business Review</OPTION> <OPTION value="29">Close Daily Dashboard</OPTION> <OPTION value="36">Close Quality Assurance</OPTION> <OPTION value="30">Close Reporting</OPTION> <OPTION value="31">Close Weekly Dashboard</OPTION> <OPTION value="18">Closing</OPTION> <OPTION value="1">Employee Lookup</OPTION> <OPTION value="5">Job Counts</OPTION> <OPTION value="44">Josh Analysis</OPTION> <OPTION value="24">Multi-Property</OPTION> <OPTION value="21">New Hire</OPTION> <OPTION value="19">Online Reservation</OPTION> <OPTION value="35">Phone</OPTION> <OPTION value="46">THD Finance</OPTION> <OPTION value="14">Tech Time Reports</OPTION></SELECT>

Can anyone get me moving in the right direction on this project?

Thanks! John

• ## Trying to navigate through url within an open IE window. VBA

Hello, I am working on a VBA code that will allow me to navigate through url links. I have as of now this

The problem is in my "Followhyperlink" code this is running in a new window. I need this to run in the same window I logged in through previously. Any ideas on how I can approach this?