UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Importing Excel Compact Pivot Table, Access 2016    
 
   
Razzbarry
post Oct 3 2019, 05:18 PM
Post#1



Posts: 4
Joined: 25-January 19



Hi,
I have a very large excel file that is updated monthly and left in a compacted pivottable. It has confidential information that obviously can't be shared but needs to be parsed for each individual employee and emailed in a table format so that it is easy to pull into excel or Access. I have tried two approaches. One convert the pivot table to classic view and remove subtotals and grand totals in Excel. It also fills down so that each transaction(this a record of each sale by sales person, supplier, etc.) has the salesperson it is for. I have been trying to find away to link the excel file to Access without manipulation in excel first but haven't been able to figure that out. This is a tool I am building for a non tech savy individual that can easily be used with little manipulation. The form in access I would like to have would allow the salespersons name be selected and the date range possibly in a split table with a option to select which items can be included plus a button to email to the person selected in the combo box. There are 26 different items from date to salesperson to commission before split to units and so forth.
Could someone impart their wisdom on how best to handle this? Given the vast amount of posts I am guessing something is already posted but I am not correctly searching for the name.

I also tried linking the spreadsheet and then runnng a table analyzer but i ran into reg errors. This is two years of data which equates to over 65k individual records in Compact mode. I think it will be less once the subtotals and grandtotals are removed.
I wish I could post a visual but I can't. Envision something like this( i hope the formating works out)
Joe Schmoe (next column>) date (next column>) invoice number(next column>) qty sold (next column>) price (next column>)total
-> Customer name
->-> Supplier name
->->-> part number 1
->->->part number2
->-> supplier number2
->->->PN 1
->->-> PN 2
-> Customer #2
->->Supplier name
->->-> part number 1
->->-> part number2
->-> supplier namer2
->->->PN 1
->->-> PN 2
Tommy Hilfiger
->Customer name
->-> Supplier name
->->-> part number 1
->->-> part number2
->->supplier number2
->->-> PN 1
->->-> PN 2
->Customer #2
->->Supplier name
->->->part number 1
->->-> part number2
->->supplier number2
->->-> PN 1
->->-> PN 2
Thanks in advance,
Razzbarry

Go to the top of the page
 
June7
post Oct 3 2019, 05:32 PM
Post#2



Posts: 1,018
Joined: 25-January 16



I am not familiar with 'compact pivot table'. Regardless, likely will require a lot of VBA code to manipulate Excel file or text file for import to Access.

For ideas on Excel import methods, review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

This post has been edited by June7: Oct 3 2019, 05:34 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MadPiet
post Oct 3 2019, 09:20 PM
Post#3



Posts: 3,366
Joined: 27-February 09



What you're describing sounds a lot like what you get with Row Level Security in DAX/PowerPivot/PowerBI, but that requires PowerBI and a PowerBI Server.

If all you want to do is create a separate file for each user that has only his records in it, you could dump all the data in Access/SQL Server and basically create something similar to a subscription. Filter the data by User, and send the user only his own data. If all your users are on the same network, you could possibly filter by username.

In Access, I think you need fGetOSUserName() function (from the old AccessWeb site) to get the current user's domain login, and you filter by that, and send the results. I know Guy(s)InACube did a video on this on YouTube, but they did it using PowerBI Desktop. (But that's at runtime... the user opens the PowerBI file, it grabs the username, and the data source filters out all the data they shouldn't see... How this works in Excel, I'm not sure.
Go to the top of the page
 
WildBird
post Oct 3 2019, 11:55 PM
Post#4


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


Why not just link to the source of the pivot table? Trying to link to the summarised part of a pivot table is fraught with issues IMO. Just get the data source and use that.

Sounds like you are doing it backwards. Data should be in a database, and use pivot tables etc for analysing and reports.

As for the emailing, that is a separate thing, but is fairly straightforward usually.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
GroverParkGeorge
post Oct 4 2019, 09:15 AM
Post#5


UA Admin
Posts: 36,185
Joined: 20-June 02
From: Newcastle, WA


I agree. Go get the source data directly if you can. Pushing it through Excel using a pivot table first seems to be the long, convoluted, way around the block.

We're assuming, of course, that you CAN actually get to the raw source data the Excel Pivot uses. Can you do that?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Razzbarry
post Oct 11 2019, 03:56 PM
Post#6



Posts: 4
Joined: 25-January 19



Thank you for the replies. The user of this spreadsheet gets the data from an SQLserver and provides me the data in excel format similar to what i described. So to answer your question .... no i don't have the direct source. I am looking for the easiest way to handle this and was using access as I didn't figure it out in excel yet. The easiest thing in the user mind is open excel, let the data upload, hit a button and check her outbox in outlook that everything was sent to the correct person. Up to this point she has manually sorted 68k lines, printed into pdf and sent. The recipients need the data in excel so they can use it which is represented in the excel file in the first column.
I found a way to generate a separate tab for each person in excel and then of course everything needs to be manually sent at this point. First I have to put it in classical view, remove sub totals, remove grand totals, show in tabular form and then repeat all items in the pivot table. It is clunky but it works. I also was able to pull the formatted excel pivot table into access, clean it up and build queries forms and macros so that all can just be sent one by one. I did this for error verification purposes but I am looking to have it auto send. If you have any advice as to which way is better I would appreciate it. I would imagine the right person could fix this in a short time. I am just a beginner given the mess to clean up. Part of this is knowing the correct terminology to describe what I am trying to do. I don't know what I don't know... From my pascal days it seems like it would be a nested loop that would catch each sales persons name on each row separate and move on to the next sales person without sending the wrong info to the wrong person. Definitely open to suggestion on this one.
Thanks,
Razzbarry

Here is the code from excel i used with some help from the internet.
Sub SplitandFilterSheet()
'Step 1 - Name your ranges and Copy sheet
'Step 2 - Filter by Department and delete rows not applicable
'Step 3 - Loop until the end of the list
Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("Splitcode")

For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=1, Criteria1:="<>" & cell.Value
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.AutoFilter.ShowAllData
Next cell
End Sub
This post has been edited by Razzbarry: Oct 11 2019, 03:59 PM
Go to the top of the page
 
June7
post Oct 11 2019, 09:10 PM
Post#7



Posts: 1,018
Joined: 25-January 16



Can you provide a workbook with data as you receive it and also example of table to import into?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MadPiet
post Oct 11 2019, 09:53 PM
Post#8



Posts: 3,366
Joined: 27-February 09



I think the Access/Excel part of this is a bit of a red herring. Create a data-driven subscription in SQL Server, and SQL Server will run the stored procedure as many times as necessary if it needs to filter the data for individual recipients. Otherwise, you can just create a regular subscription, and specify the output format as Excel, and SQL Server will take care of the rest.

Don't do this in Access/Excel, because it's all built in to SQL Server and you already have it. I like Access/Excel for some things, but this is definitely not a job for either one. Maybe check out the articles on subscriptions on the MSFT website, or they could be covered in Pluralsight on SQL Server somewhere. (Found it on Pluralsight... wish I could understand the guy, though!)
Go to the top of the page
 
GroverParkGeorge
post Oct 12 2019, 08:38 AM
Post#9


UA Admin
Posts: 36,185
Joined: 20-June 02
From: Newcastle, WA


Now that we know the original source is, in fact SQL Server, it's clear we should go there for the source data without relying on intermediate tools like Excel and Access.

As Pieter points out, you can write--or have someone write--appropriate SQL Server stored procedures to marshal the data for the report and link the report to that data source directly.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Razzbarry
post Oct 29 2019, 05:09 PM
Post#10



Posts: 4
Joined: 25-January 19



Thank you all for your input. I am trying to get access to the SQL server but so far only have the pivot table i mentioned.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 01:13 PM