User Profile
Korbinian_Heim
Copper Contributor
Joined Aug 17, 2022
User Widgets
Recent Discussions
Re: EXCEL VBA - Importing and formating a TXT file to a existing table
Thank you very much for the answer! When I try running the script I get a runtime error 52 and the debugger does mark a line of the code in yellow: txtData = Input(LOF(1), 1) Do I need to define the file which is opened to be file number one? Thanks 🙂18KViews0likes1CommentEXCEL VBA - Importing and formating a TXT file to a existing table
Hey Guys, I'm new to VBA and advanced Excel use. There is a little project I'd like to achieve and maybe some of you are able to help out with some tips 🙂 My goal is to extract data out a txt file, which is automatically generated by a flightsimulator and does contain all briefing information for the planned mission. This data then should get formated and pasted into a table I created which is used as kneeboard during the mission. The txt file is always in the same format but may vary between use cases from a segment having only 2 or sometimes 4 lines of information, this is true for more or less all segments of the txt file. I already was able to use a vba macro to put in the txt file into a excel worksheet and now I'm having trouble finding the right way to format the txt file data to fit the table I made. Here is the VBA code yet: Sub ImportText() Dim fileToOpen As Variant Dim fileFilterPattern As String Dim wsRawData As Worksheet Dim wbTextImport As Workbook Application.ScreenUpdating = False fileFilterPattern = "Textdateien (*.txt; *.html), *.txt; *.html" fileToOpen = Application.GetOpenFilename(fileFilterPattern) If fileToOpen = False Then 'MsgBox "Nix ausgewählt!" Else Workbooks.OpenText _ Filename:=fileToOpen, _ StartRow:=1 Set wbTextImport = ActiveWorkbook Set wsRawData = ThisWorkbook.Worksheets("RawData") wbTextImport.Worksheets(1).Range("A1:AZ300").Copy wsRawData.Range("A1") wbTextImport.Close False End If Application.ScreenUpdating = True End Sub Also I created a Onedrive folder, where you can see the files, the txt, the .xlsm and a pdf, which shows the table I also created in excel and where I want to paste the information of the txt into. ( I also put another example of the txt file into it, so you can see, how the segments can vary from mission to mission ). Here you go: https://1drv.ms/f/s!AljYz98plImbkPRWUef6SlSKnuw9Pg?e=bLZjv9 Do you think it is feasible to do with VBA and Excel? Thank you for any suggestions what to search for exactly or tips in any way!26KViews0likes3CommentsLittle tool project to import a html or txt, transform and export it again
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! Korbi1.1KViews0likes1Comment
Recent Blog Articles
No content to show