Import a hex file into Excel

**caycoo** · 02-15-2024, 10:22 AM

I am looking for VBA to export a binary file (in hex) into Excel.

I currently use a hex editor to open the binary file, and set it to show 100 bytes in each row. The hex output looks something as follows:

Please Login or Register  to view this content.

etc.

I then select all of this, copy and paste into Excel, and use text to columns to fill a range with all of the hex values (100 columns wide, by ~2,000 rows long).

Instead, I want to automatically extract the hex into Excel without having to first open the file in a hex editor, copy and paste, etc.

I found the following code that is able to pull out one byte at a time, but there are two issues:it imports in decimal (instead of hex), and it places one value in a row, then the next value in the next row, etc.
Instead, I need the first value in A1, the second value in B1, the third value in C1, etc. Until it has filled up to A100, then start in the next row. Byte 101 in B1, the next in B2, etc.
I hope that makes sense!

Here is the code I used:

Please Login or Register  to view this content.

I am almost there, but the main issues are:
- Need to paste directly in hex, (not decimal first and then using DEC2HEX to convert to hex)
- Fill 100 cells in row 1, before moving to row 2, fill 100 cells, move to row 3, etc.

Thanks!

**6StringJazzer** · 02-15-2024, 10:51 AM

Format the entire worksheet as Text then use this code. Sample file attached. Fully tested with your data.

Please Login or Register  to view this content.

**caycoo** · 02-15-2024, 03:03 PM

Thanks for the super fast response. I ran it and it looks great. There's just one issue that has me stumped.

I first ran the VBA code, then extracted the hex manually, i.e. copying and pasting from the hex editor, and compared the two datasets in separate worksheets in this workbook. It is sprinkled with differences all throughout. Take a look at the attached.

I used conditional formatting to highlight differences from the manual one to the VBA one. The VBA worksheet has the conditional formatting.

For some of them, it's a leading zero that gets dropped. E.g. "0E" becomes "E" (see cell L9), but then for some others it just changes to "00" (see D4 and H5). Thoughts?

I've included in the ZIP file the working Excel file (xlsb format), as well as a snippet from the import file, containing only the first 15 rows of the full file (it took too long to extract the entire thing every time).

P.S. Is there a way to force Excel to look for the import file in the current directory?? I kept failing (not finding anything), until I moved the file to c:\ and changing the VBA to point to it directly, including path (i.e. C:\import.ros)

**6StringJazzer** · 02-15-2024, 06:51 PM

When VBA converts a value to Hex, if the value is F or less it doesn't need to add a leading zero, so it doesn't. Because the result is a text string, you can't force the leading zero with the Format function. So I had to add it explicitly.

I was stumped why 122 should be x7A but shows up as 00. So I just wrote around it. This code seems to solve the problems.

Please Login or Register  to view this content.

**caycoo** · 02-17-2024, 01:03 AM

Phenomenal... Thank you so much!

**caycoo** · 02-17-2024, 01:07 AM

I almost forgot... Is there any way to force Excel to look in the workbook's current folder for the import file, so that there is no need for the entire path to be supplied in the VBA code?

If I just use "import.ros" then the output is a single 00 in cell A1, so it looks like Excel can't find the file.

**6StringJazzer** · 02-17-2024, 01:17 AM

That's exactly what I did and it worked for me.

Please Login or Register  to view this content.

**caycoo** · 02-17-2024, 04:56 AM

It's weird, for me it works the first time, then when I try again it just imports 00.

After a lot of Googling and experimenting I found a way to make it work:

Please Login or Register  to view this content.

But I have another problem that I'm struggling to work out...

How do I then export it back out to a new.ros file? I've made some changes, then I need to export the same info (from Hex!A1:CV2192 as well as the last 20 bytes in row 2193) into a new ros file, say:

Please Login or Register  to view this content.

Thanks again for all the help, I'm learning heaps!

Import a hex file into Excel

LinkBack

Thread Tools

Rate This Thread

Display

Import a hex file into Excel

Re: Import a hex file into Excel

Re: Import a hex file into Excel

Re: Import a hex file into Excel

Re: Import a hex file into Excel

Re: Import a hex file into Excel

Re: Import a hex file into Excel

Re: Import a hex file into Excel

Thread Information

Users Browsing this Thread

Similar Threads

How to make this macro export an excel to pdf file (instaid of exporting it to excel file)

How to export pdf from excel file

Export Excel records to seperate text file & compress it to protecte ZIP file

Export file from Excel

EXCEL: When I EXPORT an xls file to csv or other delimiter file , by how many ways I

Export excel file to semicolon delimited text file

Export excel file to semicolon delimited text file

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions