Excel

tenifan

Hi
I'm not a computer programmer but did a VBA course

I work with a team of people who are fairly useless with excel. A typical task is to retrieve info from a csv, create a standard report, and create accounting postings.

They usually open the csv, copy and paste it to excel, drag down formulas (usually vlookups), and pivot the data to create postings (often double-clicking pivot table cells to create backup.) It sounds straightforward, but really there are a lot of workarounds and every month there are mistakes (like not updating the pivot table range, or the pivot table range will point to the previous month's data) or at least the reports they are producing are not identical in layout (columns in different places or labelled differently).

I usually use the data menu in excel to "get external data.. From text" to import text (with the "fill down formulas in columns adjacent to data" so that I don't have to drag down formulas). I use sumifs for the postings (instead of manually refreshing pivot tables), and maybe some vba to create any sort of standardised reports. Then, the following month I just have to "refresh" the data range (usually using a file picker in VBA to chose a new file) and excel does the rest.

I'm thinking the linked data range (linked to a text file) would be too confusing for some people, so figured this may be a better way to role out the process of importing data:

1. VBA file picker to chose csv file.
2. VBA opens the data file, and for each line in the file plonks the data into column A of the "input" sheet.
3. (here's the bit I'm unsure of whether to do! is it a bit clunky?) VBA does a text-to-columns on Column A, which will also ensure the text format of each cell is correct
4. VBA adds any new columns for any formulas or calculations necessary
5. any refreshing of pivot tables or manual creation of journals can be done by the macro.

The end result is a relatively simple import process, everything else in automated, and there will be no linked ranges to confuse staff.

So, can you give a bit of feedback on which is the best solution (a) the linked table, (b) the text to columns, (c) use vba to read from the csv but use a more complex macro and NOT text to columns because it's messy or (d) something else... or (e) it's fine the way they're doing it (keep in mind there is a move towards process improvement)

Thanks!

srsly78

What database do you use? I used to do hacky stuff like this using access+excel. Mentioning access of course will be met with howls of derision

Once it's in a database you can process it row by row doing whatever kind of advanced logic you want. Might need to use some temporary tables or whatever along the way.

Step1: Import csvs into database
Step2: Clean up data, transform into desired format
Step3: Export or whatever.
Step4: Profit?

tenifan

No, not touching access databases! The ones IT created in the past are disaster areas.

The csv's don't use a database. The systems we use are ancient. The system runs batches overnight and produces csv files.. while some of the accounting postings are automated, it's still necessary to pull the csvs for analysis, reports to various parties, and even checking the oracle postings.

I don't think it's particularly hacky what I'm proposing.. just automating fairly routine tasks in order to remove human error. The VBA procedure i'm suggesting is not a lot different to what staff members are doing manually at present.
Just looking for some feedback.. I don't want to roll out a process that will cause problems down the line.

srsly78

You don't need a database as such, but you do need to read the data into memory and manipulate it. This is "database type stuff" - iterate over dataset and process row by row.

Part of your problem is you are not using a database with clearly defined fields and datatypes.

amen

No, not touching access databases! The ones IT created in the past are disaster areas

Maybe because no correctly explained what was to be done or IT didn't understand.

From your explanation all of this could be done in an automated manner and stored in a database with the pivot table accessing the data from the database.

What is generating the csv data ?

Thoie

It wasn't accounting work, but I used have people doing something similar, and created a macro for it - saved a huge amount of time and made sure stuff was accurate

It's late now, but PM me tomorrow if I forget to reply with more useful info.

Thoie

Good thing you reminded me!

I'd skip the VBA file opener, unless you really want to do it that way. Have the people open the CSV they want. Have the macro stored in their PERSONAL.XLSB on each machine, with a shortcut button in the quick menu at the top (you may need to help them put it there). Once they have the CSV open they can click the appropriate button, and it will take it from there.

I'd start by recording the macro of the manual steps they take, and tweak that.

Macro:
Step1 - save as excel
Step2 - text to columns
Step3 - add formulae
Step4 - create the pivots/journals

I know what a journal is, but not sure what you're doing with it in this instance.

The two pieces that caught me out when trying to do something similar was cleaning up rows where the text to columns had gone wrong, and finding the last line and column in order to set the range properly.

I have a piece in a macro that finds the last row reliably - it's on my other computer, will post it tomorrow.

For the text-to-columns issue, it depends on whether your data might ever have "extra" commas in there. My data occasionally did - for example
"A","b","c,d","e","f" - I'd normally expect 5 columns, but sometimes a comma could end up

tenifan

Thoie wrote: »

Good thing you reminded me!

I'd skip the VBA file opener, unless you really want to do it that way. Have the people open the CSV they want. Have the macro stored in their PERSONAL.XLSB on each machine, with a shortcut button in the quick menu at the top (you may need to help them put it there). Once they have the CSV open they can click the appropriate button, and it will take it from there.

I'd start by recording the macro of the manual steps they take, and tweak that.

Macro:
Step1 - save as excel
Step2 - text to columns
Step3 - add formulae
Step4 - create the pivots/journals

I know what a journal is, but not sure what you're doing with it in this instance.

The two pieces that caught me out when trying to do something similar was cleaning up rows where the text to columns had gone wrong, and finding the last line and column in order to set the range properly.

I have a piece in a macro that finds the last row reliably - it's on my other computer, will post it tomorrow.

For the text-to-columns issue, it depends on whether your data might ever have "extra" commas in there. My data occasionally did - for example
"A","b","c,d","e","f" - I'd normally expect 5 columns, but sometimes a comma could end up

hi Thoie,

Was that your solution on the problem you worked on? if so, I'm somewhat encouraged that your solution is practically identical to mine!

I don't think it's possible to "have the user open a csv, then do a text to columns... " .. excel will open a csv already split into columns, and not only that will make a mess of number formatting. also, any editing directly to the csv (like pivot tables) would be in vain because it would subsequently need to be saved as an excel file.

I'm leaving the file picker in though. The excel file will act as an application in itself, "importing" a csv, and outputting the processed file to a new workbook. As the application is stored on the network drive, people will always be using an up-to-date version (as opposed to a version stored on their local machine)

The text to columns should never go wrong, as these are system-generated files and always in the same format. Might be worth adding some error checking to ensure the format is as expected (e.g. match the first line string which contains column headings to a string hard-coded into the vba code)

As for finding the last row.. usedrange should work, as no other editing will have been done on the worksheet to where the data has been imported. Alternatively, counta (-1 for the headers) on a column which always has data.

amen

As the application is stored on the network drive, people will always be using an up-to-date version (as opposed to a version stored on their local machine)

until someone downloads a version to their local pc

as these are system-generated files and always in the same format.

maybe. Does the person who maintains the system that generates the files know what you are doing with the data/format ?

Do you have pass all the regulations/rules within your organisation for manipulating data ?

tenifan

amen wrote: »

until someone downloads a version to their local pc

Doesn't happen so much actually.. as we don't have local machines... just those networked boxes.

amen wrote: »

maybe. Does the person who maintains the system that generates the files know what you are doing with the data/format ?

huh?

amen wrote: »

Do you have pass all the regulations/rules within your organisation for manipulating data ?

i don't think our organisation has a data manipulation policy.

mccy02

Hi


I have an excel problem and I hope someone can help me solve it. I have a Swift Excel file that I am uploading to Sharepoint, but it won't open in Sharepoint, and give me an error message saying that there are unsupported features, specifically:

"Custom toolbar attached to the workbook (also called attached toolbar)"

Can anyone tell me how I might resolve this in the file on my pc so that the uploaded file will open in Sharepoint please?

Thanks!

Evil Phil

mccy02 wrote: »

Hi


I have an excel problem and I hope someone can help me solve it. I have a Swift Excel file that I am uploading to Sharepoint, but it won't open in Sharepoint, and give me an error message saying that there are unsupported features, specifically:

"Custom toolbar attached to the workbook (also called attached toolbar)"

Can anyone tell me how I might resolve this in the file on my pc so that the uploaded file will open in Sharepoint please?

Thanks!

You need to start a new thread for this question.