Hi Guys,
I'm new to this forum and I joined because I have something in mind, where I might be able to receive a little advice and help in here.
In Excel I do not have a lot of experience when it comes to advanced use, I know of all these things like VBA and Power Query but I don't know if I can utilize Excel to get the solution I'm seeking.
That's why I joined up here, maybe some of you can tell me, if my project is possible in Excel and if it is maybe give me some advise on where to best start my further research and learning.
Now to my little idea:
In the end I want to have a tool, which is able to do following steps with as less mouse klicks as possible (prefferable 1):
- Import a local html or txt file (depends on which of the two has the better format to get on with, no need to use both)
- Get specific information from the importet file into the correct format
- (Maybe add some information into the new created table)
- Export the new table either into a new excel file or maybe even a PDF or DDS
Sounds not too complicated in the first place and I think I'd be able to pull it off, if there weren't some little querks to this process.
To understand why it might get a bit more complicated I need to explain the information I want to process and the format I want to have it in in the end a little further.
The data I want to start with is getting exportet from a flightsimulator and is available in either a html or a txt file. It contains the briefing for a planned flight with times, frequencies, weather, airbases, IFF and some more. This data is neccessary for the briefing process and has to be available later in time on a kneeboard during the flight. Pilots use a sheet of paper called datacard or lineup card which attaches to a clipboard strapped to their legs during the flight. Anyways, the format the data is available in is completely different in the html or txt. Only one of the filetypes can be generated by the flightsimulator and therefore the Excel tool only needs to be able to process either one, not both. Which one to use is to be determined by the format of the data, whichever of the two is easier to process in Excel will be the winner.
Thats the first problem I might have to face: The data is not available in a typical table format and to make it worse the size of the sections with information inside the briefing file differ each time, depending on the flightplanning. The order of the sections will be always the same, but sometimes a section may only have one line and next time three or four. The export unfortunately does not leave blank space to accord for the maximimum size of each section, so that the number of lines would stay the same, simply sometimes with information and sometimes not.
That's why I think html would be easier to handle in comparison to a txt, as it has at least some structure already built into it and each section is defined more clearly.
After the information was importet successfully I think the hardest part could already be through.
Now the structured and sorted information 'only' need's to be transferred to it's final format. This format will be the already mentioned datacard.
This is a game of data out of cell A5 in one sheet needs to be in cell U27 in another sheet. Should be possible with Power Query in Excel?
The last step is to output the datacard into a usable file, either a xlsx, a pdf or maybe even a dds graphic. Maybe I also might be better situated if I have the option to choose each time, which output file format I prefer.
To give you a better idea of what material I have on hand I will attach the exported briefing in both file formats and a example datacard.
Be aware that by far not all information there is on the datacard can be supplied by the briefing file, but thats no problem, as I will drasticly change the layout and information found on the datacard to mirror a real datacard I got my hands on some time ago a lot closer. To get an idea of what I want to achieve, it is a lot better than nothing.
I hope I was able to explain my thougts well enough to be understood for native english speaking guys as well, as I'm from germany ;)
Maybe some of you might be able to lead me, what to do and where to search to accomplish my project.
Thank you already for even reading all this text.
I'm really looking forward to hear from you! :)
https://1drv.ms/f/s!AljYz98plImbiqlRsnvHoVLNrhaQJQ
I already tried to process the html, but Power Query did not recognize all information and only split some sections into usable tables, even though the online preview in the import screen showed to whole html file.
Thank you all!
Korbi
