Recent content by sxschech

For a visual to accompany the Doc Man's explanation in post #9, here are some screen shots from task manager and MS-Access for a scheduled task that I run.

It was a careless oversight on my part when I added the If statement logic to test for whether to keep excel open. Probably did ctrl+x rather than ctrl+c when pasting into the Else.

Thanks for the various suggestions. I thought things were working when I left on Friday, however today, any variation of the code still presented issues. After a bit more searching, today, I think I discovered why the original code had worked fine until last week and seemed to work on Friday...

So is okay the way the code is then?

Made some changes and added the Close statement. The timing issue seems to have moved up the chain... ...was able to get rid of the message box, however had to repeat the Set Statement to avoid an error on wb.Activate, probably also relating to the timing since the second one doesn't generate...

I thought this code was working properly, however, yesterday noticed that the excel file was showing as locked and excel wasn't open (from the perspective of the taskbar not having an underline below the icon). When I went into Task Manager, Excel was there under Background Processes. I think...

Here is an example of using SQL to append data from excel and specify the row number. If you are doing a make table or other type of query, change the sql statement as needed. stfilename is a variable for the file name (can hard code if you prefer) DOCS$A is the sheet/tab name along with the...

Even so, your technique is an improvement to what I started with by trying to put a formula in each cell separate from the actual pivot table.

After a bit more web searching found out how to see the formula. Apparently I misidentified the part in your explanation about where the drop down box is and that is why couldn't see or edit the formula. The video below showed the steps you wrote about in post #11 and once the drop down box is...

Thanks, I thought I tried looking at the calculated field and it only had the option to add not view or edit existing. Maybe missed a step? Found the formula by clicking on List Formulas. It ended up adding a new tab to the spreadsheet with the information. Seems a bit odd rather than being...

Yes, I understood there was a calculated field involved. I haven't used Pivot tables much so not familiar with all its capabilities. My question was how did you create the calculated field in the pivot. I see in the detail there is a formula that counts up the cols =CountA(I2:M2) and a simple...

I see that you added two cols to the data tab. Tried recreating in my local file by adding the two cols and adjusting the Change PivotTable Data Source Range to include col A through Col O. Not sure how the formula or calculation was performed on the pivot under label Sum of Weekly Total (col H)

Since you mentioned the file does not match the image, I downloaded the zip I posted and the file looks fine to me, not sure what changed when you opened it. Have attached screen shot from that summary page. Perhaps I didn't explain the issue well? The data in the pivot from Col A through...

Here is a sample. Cleared out data that is not needed for summary rather than obfuscating. Truncated lead and discipline.

Have exported access query data to excel. Created a pivot table to count up by Person. Was asked to add cols for days of week and provide count per day along with a col at the end that is the sum of the daily counts minus the original total count. I looked at a few examples on various sites...