Generate report from excel

Hi,

I´m having a schoolproject where I´m creating a calculation program for hydraulic cylinders depending of what positions they have.
What I want is that when calculations is done, I will be able to generate a report that automatic returns numbers from my calculations.
I also want it to generate a different reportnumber each time a report is generated.

When saving the report I would like it to be stored in a folder named as the reportnumber.
Finally I want to be able to search for the report by use of a searchengine in my excel workbook.

Does anybody have something similar to post me, or some tips to guide me on the way.

Any ideas are much appreciated

Best regards
Oeysbrei

arlu1201,

Thanks for youre reply, but my file is all in norwegian and contains a whole lot of unsorted data that only I know whats for.

What I basicly want is a reportform that collects data from my workbook to be generated by pushing a button.
When saving I would like it to be saved as a single file in a reportfolder.

Oeysbrei

This is me shooting from my hip, but that "whole lot of unsorted data" thing is probably the first big problem.

If it were me, I would write a "template" xls, with a tab labeled "input" where you would copy-paste data in, and then a tab labeled "report" that would point to the data on the "input" tab.

Saving a different name... would be accomplished by the user with the "save as" function.

I mean, you could do all of this with a macro or something, but based on what you're saying, I'm not seeing where it would benefit you much to go to the bother of it.

Hi,

Anybody?

arlu1201,

Thanks for youre reply.
In the report I will collect data from differrent sheets, but for a start I will need the following data:

Kalkulasjon Formelinput
L3:L9 C17
P13 I19:I24
Q13 B20
R3

What I also need the report to contain, is the date that will be set automaticaly.
Also as I wrote earlier a reportnumber that will be generated automaticaly.

Regards
Oeysbrei

Hi,

Prompt

arlu1201,

I want the report to be one sheet, and saved as a single file with filename as reportnumber.
I dont know if it has to be done by using VBA, but I guess it has. I have tried different myself, but have no idea how to solve this challenge.

I´m pretty new at this, but learning slowly. So i´m glad I found this forum with people like you guys.

Hope this answered youre question

Regards
Oeysbrei

Søren Larsen,

Thank you Søren.
I will keep on trying, so I will test these subs and hopefully solve bit by bit

Regards
Oeysbrei

arlu1201,

I need it to generate a new reportnumber everytime a new report get saved. So when new data gets inserted in the sheet Kalkulasjon (Calculation), I want to generate a new report.

Regards
Oeysbrei

arlu1201,

It doesent have to be cleared, because when new data gets inserted in the calculationform the reportform will change.
But when saving the report, the macro can open up the calculationform again.

I know this is a big task, and hoping i´m not asking to much
I appreciate all the help and effort you put in to this.

Regards
Oeysbrei

arlu1201,

Have you had any time looking in to this? I have tryed myself to solv it bit by bit, but havent come any further.

Regards
Oeysbrei

arlu1201,

I had planed to use the form as it is so it would be easy to add data, but it needs to generate a new reportnumber to be located top right corner everytime it gets saved.
This also needs to be the filename, so it will be easy to search for it.

I have got a code to save it as a single PDF file, but I cant make the form to show in one page.

Regards
Oeysbrei

Hi arlu1201,

I have been trying to solv this task without luck, I only mess things up. luckaly I have made a copy of the workbook that have come usefull several times.
Any ideas how I can open up the reportsheet as a single PDF file. The code I posted earlier doesent work as I want, or maybe it´s me that´s not doing it right.

If I only could get the PDF to open, I could name it manually befoure saving it.
The entire workbook needs to be finish in 10 days, so I have to keep it simple the days I have left.

Thanks for all youre help

Best regards
Oeysbrei

arlu1201

The reportform is finish, and I thought I had the code work as I wanted.
The report get saved and opened as I want.

The only problem I have now is that everytime I open the workbook after it has been closed, the filname gets saved counting from one again.
I need something in the code that remember the last number that was saved.

Is there a way to do this?

This is the code i´m using

Please Login or Register  to view this content.

Regards
Oeysbrei

arlu1201,

That sounds like a god idea.
This is a school project for a company that I hope will be satisfied enough to develop it further to suit their needs.
Youre solution will make it able to be used eighter on a local computer or if used from a server.

But is there a way I can implement that to the existing code?

Oeysbrei

Hello,

Bump

Hi,

This code makes me able to save a sheet as a single PDF were the filename gets TTS-1 first time saved, and TTS-2 next time and so on.
The problem I have is that when the workbook gets opened again after been closed, it starts counting from one again.

Does anybody have any ideas what to do so that it remembers the last number saved?

Please Login or Register  to view this content.

Regards
Oeysbrei

Originally Posted by Oeysbrei

Hi,

This code makes me able to save a sheet as a single PDF were the filename gets TTS-1 first time saved, and TTS-2 next time and so on.
The problem I have is that when the workbook gets opened again after been closed, it starts counting from one again.

Does anybody have any ideas what to do so that it remembers the last number saved?

Please Login or Register  to view this content.

Regards
Oeysbrei

Anyone plz?

Thank you,

I will finally mark this solved

Regards
Oeysbrei