SOLVED

Automating inserting/deleting/editing/copying rows in a xlsx in online sharepoint?

%3CLINGO-SUB%20id%3D%22lingo-sub-2133997%22%20slang%3D%22en-US%22%3EAutomating%20inserting%2Fdeleting%2Fediting%2Fcopying%20rows%20in%20a%20xlsx%20in%20online%20sharepoint%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2133997%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22_3xX726aBn29LDbsDtzr_6E%20_1Ap4F5maDtT1E1YuCiaO0r%20D3IL3FD0RFy_mkKLPwL4%22%3E%3CDIV%20class%3D%22_292iotee39Lmt0MkQZ2hPV%20RichTextJSON-root%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3CIMG%20border%3D%220%22%20%2F%3E%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThere%20are%20files%20that%20contain%20multiple%20tables%20organized%20like%20this.%20I%20am%20an%20intern%20and%20each%20time%20I%20do%20something%20for%20example%20give%20a%20keyboard%20that%20is%20free%20to%20an%20employee%2C%20I%20must%20edit%20the%20file%20copy%20the%20row%20of%20that%20item%20and%20pasting%20it%20in%20the%20table%20of%20the%20section%20where%20the%20employee%20works.%20Then%20I%20must%20delete%20it%20from%20free%20and%20I%20must%20change%20some%20columns%20(like%20owner)%20in%20the%20table%20where%20is%20the%20full%20list%20of%20all%20the%20items.%20I%20am%20interested%20a%20way%20that%20inserting%20in%20a%20script%2Fx%20the%20id%20of%20the%20product%20and%20the%20id%20of%20the%20employee%20it%20does%20this%20automatically.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20would%20like%20about%20advice%2Csuggestions%20for%20working%20with%20it%2C%20Is%20recommended%20for%20automating%20with%20AHK%20or%20Powershell%20or%20what%20would%20be%20better%3F%20Thanks%20for%20your%20time.%3C%2FP%3E%3CDIV%20class%3D%22_1hwEKkB_38tIoal6fcdrt9%22%3E%3CDIV%20class%3D%22_3-miAEojrCvx_4FQ8x3P-s%22%3E%3CDIV%20class%3D%22_1UoeAeSRhOKSNdY_h3iS1O%20_3m17ICJgx45k_z-t82iVuO%20_3U_7i38RDPV5eBv7m4M-9J%20_2qww3J5KKzsD7e5DO0BvvU%22%3E%26nbsp%3B%3CDIV%20class%3D%22_JRBNstMcGxbZUxrrIKXe%20_3U_7i38RDPV5eBv7m4M-9J%20_3yh2bniLq7bYr4BaiXowdO%20_1pShbCnOaF7EGWTq6IvZux%20Z-VR19FVnE3nOS0_BU4Wy%22%3E%26nbsp%3B%3CDIV%20class%3D%22_3U_7i38RDPV5eBv7m4M-9J%22%3E%26nbsp%3B%3CDIV%20class%3D%22_3U_7i38RDPV5eBv7m4M-9J%22%3E%26nbsp%3B%3CDIV%20class%3D%22_3U_7i38RDPV5eBv7m4M-9J%22%3E%26nbsp%3B%3CDIV%20class%3D%22OccjSdFd6HkHhShRg6DOl%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2133997%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2136762%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20inserting%2Fdeleting%2Fediting%2Fcopying%20rows%20in%20a%20xlsx%20in%20online%20sharepoint%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F966397%22%20target%3D%22_blank%22%3E%40Elqueaprende%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20probably%20is%20an%20easier%20way%20to%20keep%20track%20of%20inventory%20than%20even%20what%20you're%20suggesting.%20Your%20first%20sentence%20says%20%22There%20are%20files%20that%20contain%20multiple%20tables%20organized%20like%20this.%22%20but%20%22this%22%20never%20appears%20(if%2C%20that%20is%2C%20you%20were%20planning%20to%20insert%20an%20image%20and%20then%20forgot).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMight%20I%20suggest%20that%20you%20attach%20a%20copy%20of%20the%20actual%20spreadsheet%3F%20Not%20an%20image%3B%20the%20actual%20spreadsheet(s).%20Any%20real%20names%20should%20be%20replaced%20by%20the%20names%20of%20Disney%20characters%20or%20something%20else%2C%20so%20that%20you're%20not%20compromising%20confidential%20information.%20If%20that's%20not%20possible%2C%20a%20copy%20that%20closely%20resembles%20your%20actual%20files%2C%20so%20as%20to%20give%20us%20an%20idea%20of%20what%20this%20all%20looks%20like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20own%20sense%20is%20that%20it%20should%20be%20possible%20to%20simply%20create%20a%20transactional%20database%20that%20keeps%20track%20of%20assignments%2C%20returns%2C%20etc.%2C%20using%20date%20and%20ID%23s%20and%20items%20codes...something%20like%20that.%20Just%20automating%20what%20you're%20doing%2C%20if%20what%20you're%20doing%20is%20not%20efficient%20in%20the%20first%20place%2C%20would%20simply%20be%20automating%20something%20that%20isn't%20efficient.%20Let's%20try%20to%20design%20it%20intelligently%20first.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2145905%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20inserting%2Fdeleting%2Fediting%2Fcopying%20rows%20in%20a%20xlsx%20in%20online%20sharepoint%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2145905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello!%20.Thanks%20for%20your%20patience.%20These%20days%20I%20was%20overwhelmed%20but%20I%20made%20time%20for%20making%20what%20you%20suggested%2C%20here%20you%20have%20example%20tables.%20I%20deleted%20the%20rows%20and%20filled%20the%20with%20example%20info.%20I%20am%20forced%20to%20work%20with%20this%20(I%20am%20intern%20so%20I%20don't%20think%20they%20will%20allow%20me%20to%20change%20it%20and%20they%20told%20me%20to%20leave%20like%20this%20the%20forms%20in%20the%20sharepoint).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20There%20is%20a%20Monitor%20Benq%20free%20in%20the%20%22FREE%22%20table%20and%20I%20give%20it%20to%20Jessy%20Sar%20from%20the%20Media%20Department.%20So%20I%20take%20the%20row%20of%20that%20Monitor%20and%20I%20move%20it%20to%20it%20to%20the%20table%20MEDIA%20then%20I%20must%20edit%20the%20name%20and%20Surname.%26nbsp%3B%20And%20in%20the%20Table%20Office%20I%20also%20must%20edit%20the%20info%20of%20that%20monitor.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2146052%22%20slang%3D%22en-US%22%3ERe%3A%20Automating%20inserting%2Fdeleting%2Fediting%2Fcopying%20rows%20in%20a%20xlsx%20in%20online%20sharepoint%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2146052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F966397%22%20target%3D%22_blank%22%3E%40Elqueaprende%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20you%20may%20be%20%22just%20an%20intern%22%20but%20your%20gut%20is%20right...there%20should%20be%20a%20better%20way%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you%20have%20is%20not%20really%20a%20way%20to%20use%20Excel.%20Instead%20it's%20an%20abuse%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20precedent%20for%20this%20kind%20of%20thing%2C%20but%20I%20would%20be%20willing%20to%20bet%20lots%20of%20money%20that%20this%20began%20as%20a%20way%20to%20track%20equipment%20that%20was%20done%20on%20big%20sheets%20of%20ledger%20paper.%20Lots%20of%20rows%20and%20columns%20to%20neatly%20lay%20out%20data.%20On%20paper%2C%20before%20computers%20were%20ubiquitous%2C%20it%20was%20a%20reasonable%20solution.%20Done%20with%20pencil%2C%20an%20easy%20matter%20of%20moving%20a%20line%20from%20one%20place%20to%20another%2C%20from%20inventory%20to%20being%20assigned%20to%20person%20X%2C%20and%20back%20again.%20Move%20over%20there%2C%20copy%20data%3B%20erase.%20Copy%20back%2C%20erase.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20Excel%20came%20along%20with%20all%20those%20rows%20and%20columns%20and%20somebody%20said%2C%20%22Hey%2C%20we%20could%20computerize%20our%20way%20of%20tracking%20equipment!%22%20So%20they%20took%20the%20manual%20process%20of%20copyng%20and%20pasting%20and%20erasing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20that's%20NOT%20really%20using%20Excel%3B%20it's%20not%20beginning%20to%20take%20advantage%20of%20Excel's%20abilities%20to%20record%20transactions%20and%20then%20present%20a%20snapshot%20of%20where%20things%20are%20and%20who%20has%20them.%20All%20it's%20doing%20is%20making%20the%20manual%20task%20of%20moving%20and%20erasing%20a%20bit%20more%20reliable%20(by%20copy%20and%20paste).%20But%20the%20truth%20is%20that's%20more%20accurately%20described%20as%20abusing%20Excel.%20Excel%20could%20do%20a%20far%20better%20job%20of%20it%2C%20far%20more%20reliable%2C%20less%20labor%20intensive.%20It%20would%20require%20re-thinking%20how%20the%20inventory%20is%20tracked.%20Whoever%20has%20the%20decision%20making%20authority%20considers%20it%20cheaper%2Feasier%20to%20have%20an%20intern%20do%20things%20the%20old%20way%20than%20to%20really%20think%20about%20the%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I'm%20sorry%20to%20have%20to%20say%20it%3A%20You're%20stuck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

There are files that contain multiple tables organized like this. I am an intern and each time I do something for example give a keyboard that is free to an employee, I must edit the file copy the row of that item and pasting it in the table of the section where the employee works. Then I must delete it from free and I must change some columns (like owner) in the table where is the full list of all the items. I am interested a way that inserting in a script/x the id of the product and the id of the employee it does this automatically.

I would like about advice,suggestions for working with it, Is recommended for automating with AHK or Powershell or what would be better? Thanks for your time.

 
 
 
 
 
 
6 Replies

@Elqueaprende 

 

There probably is an easier way to keep track of inventory than even what you're suggesting. Your first sentence says "There are files that contain multiple tables organized like this." but "this" never appears (if, that is, you were planning to insert an image and then forgot).

 

Might I suggest that you attach a copy of the actual spreadsheet? Not an image; the actual spreadsheet(s). Any real names should be replaced by the names of Disney characters or something else, so that you're not compromising confidential information. If that's not possible, a copy that closely resembles your actual files, so as to give us an idea of what this all looks like.

 

My own sense is that it should be possible to simply create a transactional database that keeps track of assignments, returns, etc., using date and ID#s and items codes...something like that. Just automating what you're doing, if what you're doing is not efficient in the first place, would simply be automating something that isn't efficient. Let's try to design it intelligently first.

@mathetes 

Hello! .Thanks for your patience. These days I was overwhelmed but I made time for making what you suggested, here you have example tables. I deleted the rows and filled the with example info. I am forced to work with this (I am intern so I don't think they will allow me to change it and they told me to leave like this the forms in the sharepoint).

 

Example: There is a Monitor Benq free in the "FREE" table and I give it to Jessy Sar from the Media Department. So I take the row of that Monitor and I move it to it to the table MEDIA then I must edit the name and Surname.  And in the Table Office I also must edit the info of that monitor.

 

best response confirmed by Elqueaprende (New Contributor)
Solution

@Elqueaprende 

 

Well, you may be "just an intern" but your gut is right...there should be a better way to do it.

 

What you have is not really a way to use Excel. Instead it's an abuse of Excel.

 

There is precedent for this kind of thing, but I would be willing to bet lots of money that this began as a way to track equipment that was done on big sheets of ledger paper. Lots of rows and columns to neatly lay out data. On paper, before computers were ubiquitous, it was a reasonable solution. Done with pencil, an easy matter of moving a line from one place to another, from inventory to being assigned to person X, and back again. Move over there, copy data; erase. Copy back, erase.

 

Then Excel came along with all those rows and columns and somebody said, "Hey, we could computerize our way of tracking equipment!" So they took the manual process of copyng and pasting and erasing.

 

But that's NOT really using Excel; it's not beginning to take advantage of Excel's abilities to record transactions and then present a snapshot of where things are and who has them. All it's doing is making the manual task of moving and erasing a bit more reliable (by copy and paste). But the truth is that's more accurately described as abusing Excel. Excel could do a far better job of it, far more reliable, less labor intensive. It would require re-thinking how the inventory is tracked. Whoever has the decision making authority considers it cheaper/easier to have an intern do things the old way than to really think about the process.

 

So, I'm sorry to have to say it: You're stuck.

 

 

@mathetesThanks a lot, I appreciate your time. Maybe can I create a local database in my personal computer with Maria DB and then define and set how must be exported the query results in csv and then upload it and replace that file or would be too dangerous if other person is also working on the file? Maybe for the concurrency editing the csv files in sharepoint?

@Elqueaprende 

 

Hard to say what the most productive course of action could be. I have no personal experience with Sharepoint, don't know how it interacts with Excel, etc., etc.

 

As for the design, I would envision some set of tables such that

  • equipment is identified by some ID
  • individuals are identified by ID
  • transactions are tracked
    • for the assignment of equipment to individuals
    • for retrieval of equipment from individuals (return to storage)

 

From those transactional records, a dashboard of some kind can be used to provide snapshots at any point in time of where things are, who has them, for how long, etc. etc.

 

Doing this would take some database knowledge as well as process and procedural knowledge. It could be a great learning experience.

@mathetes 

Thanks a lot for your time and help. Probably as you said I am stuck but I will try anyway for learning.