Group,

Is there a function in VBA that will convert hex into ascii. Example assume I have 0x39 and I want it to convert it to ascii, or 9. Same goes for the alpha characters as well A thru Z both lower case and upper case.

Tony

Is there a function in VBA that will convert hex into ascii. Example assume I have 0x39 and I want it to convert it to ascii, or 9. Same goes for the alpha characters as well A thru Z both lower case and upper case.

Tony

- Hex to ASCII converter in Excel?
- Hex to ascii using macro
- Converting Long HEX string to ASCII
- Converting Hex to Dec in VBA and making code efficent
- VBA macro to convert decimal to hex and back
- Faster way to convert hex to ieee754?
- Serial Data to ASCII
- VBA code to find lookup value from two data sets
- VBA code to sum up the values by date for multiple criteria
- VBA code to overwrite excel file in destination folder if exist
- VBA code to copy data out of a cell for use in VBA
- Create VBA macro to replace formula's in cells of all sheets in workbook
- Problem Copying Results from Kickbutt VBA Function to Different Sheet
- USD $10.00 vba code to delete rows in workbooks
- How to Use a VBA Macro to Sum Only Visible Cells
- Is it possible to write a vba code to run in a worksheet that has yet to be created?
- VBA code to search for and delete Form Controls on an Excel 2007 spreadsheet
- VBA Code To Sort Column
- Bug: Add A Secured Vba Module To Sourcesafe.
- VBA code to copy data to another tab based on range of numbers and values
- How to store VBA code to a variable to avoid compiler errors and for later execution.
- VBA: How to open and read data from 1 workbook and write into another workbook?
- Formulas: or VBA - Need to be advised whenever a cell is equ
- Decimal To Ascii Conversion

We would like to convert the below hex file(in the first tab) to the attached output file(in the second tab) which is ascii. Could you please give us a macro which will convert the hex to ascci data in the below formats. This will be very helpful for us in troubleshooting an issue.

I did an export of our users from Active Directory using CSVDE.exe. This generated a csv file which I then work with in Excel and word.

My problem is that any field that has either a carrige return, or an accent in the name come out as a long HEX string with an x' at the start and an ' at the end instead of the proper word.

For example Stephane (The e should have an accent, but I can't display that here) comes out as X'5374c3a97068616e65'.

What I need help with is a formula/macro/vba code, that would search for all cells whos data starts with x'. Once found I need to remove the x' from the begining and the ' from the end. Then pair each 2 characters of hex together and convert then to ascii. If we take the example above, X'5374c3a97068616e65' would become 5374c3a97068616e65, then 53 becomes S, 74 becomes t, c3 and a9 form e with an accent, 70 is p... and so on and so forth. Then once that's all done, put the converted name/title back into the orginal field.

I thank everyone in advance for any help they can give me with this.

If you need more information, examples, or know of a better way for me to do this, please feel free to let me know.

Thanks

James

Wondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..

I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.

Sub HexConversion() Dim x As Integer, y As Integer Dim H2D As Long With Sheets("Floats") For x = 1 To 1000 'row number y = 11 'column K H2D = HexToDec(Cells(x, 1)) Cells(x, y) = -(H2D > 32768) Cells(x, y + 1) = -((H2D - Cells(x, y) * 32768) >= 16384) Cells(x, y + 2) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384) >= 8192) Cells(x, y + 3) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192) >= 4096) Cells(x, y + 4) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096) >= 2048) Cells(x, y + 5) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048) >= 1024) Cells(x, y + 6) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024) >= 512) Cells(x, y + 7) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512) >= 256) Cells(x, y + 8) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256) >= 128) Cells(x, y + 9) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) * 128) >= 64) Cells(x, y + 10) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) * 128 - Cells(x, y + 8) * 64) >= 32) Cells(x, y + 11) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) * 128 - Cells(x, y + 8) * 64 - Cells(x, y + 9) * 32) >= 16) Cells(x, y + 12) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) * 128 - Cells(x, y + 8) * 64 - Cells(x, y + 9) * 32 - Cells(x, y + 10) * 16) >= 8) Cells(x, y + 13) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) * 4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) * 128 - Cells(x, y + 8) * 64 - Cells(x, y + 9) * 32 - Cells(x, y + 10) * 16 - Cells(x, y + 11) * 8) >= 4) Next x End With End SubI am using this Function to convert Hex to Dec since there is no VBA built in function, which I got from VBAExpress.com: http://www.vbaexpress.com/kb/getarticle.php?kb_id=307

Public Function HexToDec(Hex As String) As Double Dim i As Long Dim j As Variant Dim k As Long Dim n As Long Dim HexArray() As Double n = Len(Hex) k = -1 ReDim HexArray(1 To n) For i = n To 1 Step -1 j = Mid(Hex, i, 1) k = k + 1 Select Case j Case 0 To 9 HexArray(i) = j * 16 ^ (k) Case Is = "A" HexArray(i) = 10 * 16 ^ (k) Case Is = "B" HexArray(i) = 11 * 16 ^ (k) Case Is = "C" HexArray(i) = 12 * 16 ^ (k) Case Is = "D" HexArray(i) = 13 * 16 ^ (k) Case Is = "E" HexArray(i) = 14 * 16 ^ (k) Case Is = "F" HexArray(i) = 15 * 16 ^ (k) End Select Next i HexToDec = Application.WorksheetFunction.Sum(HexArray) End FunctionThanks

attached code using VBA. Is this a 'fast' way to do this operation? My code

spends 90% of the time performing this operation. I'm hoping there is a

faster way. Any comments are apprectiate.

Thanks!

Mark

'To type cast in VB you need to use the API to copy the contents of one data

type into another....

'turns hex data into floating point ieee754 standard

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"

(Destination As Any, Source As Any, ByVal Length As Long)

Function Hex2Ieee754(b1, b2, b3, b4)

Dim bytArray(0 To 3) As Byte

Dim fResult As Single

' load your data (40 B8 00 00) into a byte array creates 5.75

' note the order is reversed

bytArray(3) = "&H" + b1

bytArray(2) = "&H" + b2

bytArray(1) = "&H" + b3

bytArray(0) = "&H" + b4

' copy into the float

CopyMemory fResult, bytArray(0), 4

' print the result (5.75)

'Debug.Print fResult

Hex2Ieee754 = fResult

End Function

I have a question that I am not sure there is an answer for - but hopefully someone may have come across this somewhere or has some knowledge.

I am trying to get excel to read serial data from a COMS port as ASCII. In this instance, a cheque reader which scans the MICR on the bottom of the cheque.

There are third party programs that will allow you to do this (many are even shareware) - but I need to try and find a method of having excel perform the function of reading the data from the port, and converting it to ASCII characters.

Has anyone ever come across an article/plug in or other interface with excel that will help? Also, can anyone comment on whether this may be possible with VBA?

Thanks in advance for the help.

Cheers

Rob

Hi All,

I have reported the above question in forum (excelforum.com) with the attachment.

Any help would be greatly appreciated.

Many Regards

Manav

Hi All,

Respected Members please accept my sincere thanks for all your esteemed help given through this channel.

I have attached a sample worksheet which I use in work. My aim is to find job nuumbers on sheet1 in the dataset on sheet 2(column A) and return the corresponding values iolumn B & C on sheet 2. Please note that one job number can be paid more than one time in the past months(eg. 3300023104), and therefore the requirement is to total the amount of money paid in all months, on a particular job reference. The output i require on sheet 1 is "month paid in" and "amount paid ". If a job is paid just once then simply get the month and amount on sheet 1. But i dont know if a paricular job like 3300023104 has been paid loads of times in several months, how can we list the months as well as the amount. If months is not possible then only amount can do...

My objective is to find out how much money has been paid before on the jobs mentioned on sheet 1 column A (my current month invoice). I already have a vba code to trace any duplicates within column A on sheet 1. For instance job ref 3300023104 has been repeated a few times on sheet 1.

Please advise at your convenience.

Thank you

Manav

The details are in the attached spreadsheet. Thanks in advance for your help!

1. I require VBA code to overwrite excel file in destination folder if exist.

2.

Actually my macro code first sort,save and close the file, then it copy and paste this excel file to destiny network drive.

All things are working here except copy and paste. I am able to copy and then move file to destiny but I want to paste it and not move.

Help in this regard really appreciated

Had a quick look and couldn't find anything like this that has been answered already.

I would like to do the following.

Background.

We have approx 30 technical claims handlers who each use a spreadsheet to record their individual caseloads with various dates etc. All these spreadsheets live in a communual folder on a network drive.

To assist the team manager I want to build something that will analyse each persons data for purposes of monthly 1 to 1's etc. That in itself is a doddle.

Due to Head Office and their silly rules I cannot do the obvious and add a worksheet in (don't even go into the why and why nots) so I plan on a standalone spreadsheet to copy across an individuals data and work things out there. (Again a doddle)

The team managers are not exactly excel savvy, so the idea is to automate as much as possibly using VBA and macro. What I plan is a dropdown box - they select the persons name, click a button and hey presto it all happens. (99% of that I can do)

The problem

I envisage a vlookup off the name in the dropdown box, the second column would contain the filepath and filename of that persons spreadsheet. So if I picked Josh I would get s:filepathjosh.xls

What I don't know how to do in VBA is to say "take the contents of cell C1, and use that as the name of the spreadsheet to open

e.g. Workbooks.Open Filename:="S:filepathjosh.xls."

what code do i put in so it reads C1 as the link and puts it in.

I hope that makes some kind of sense.

Thanks

Lynton

I am using Excel 2007.

I have a group of large databases that were misconfigured, and now I need to edit the formula's in each cell. There are more than 12,000 rows of 6 collumns each, on up to 11 sheets per workbook, and 8 workbooks that I need to fix. Within a workbook, there are up to 42 different formula's that need to be replaced. I know how to do this with the built in Find/Replace, however that is too much work, so I want to create a workbook level VBA macro to do this for me. The problem is that I am just beginning to learn how to create VBA macro's, so I don't really know how to do this.

Here is an example of what I need to do. On all sheets within the workbook, I first need to replace the formula in any cells that contain the formula "=A4" with the formula "=B1". Then I need to replace the formula in any cells containing formula "=A5" with formula "=C1". This continue's until formula "=A45" is replaced by formula "=AQ1". Please help me to create a "Sub FindAndReplaceFormula()"

Thank you for your kind assistance

JMDIGS

Below is the code I have used. The results of the function call return an unknown amount of rows with 10 columns.

VB:As you can see the destination I use is only one cell so it should paste with no problem but it doesn't. The code runs fine when I paste to any row in column A but once I paste to a different column it bugs out. I don't understand why it is creating these errors.mon = month(DateValue("1-" & Target.Value & "-2000")) Call Find_Range(mon, Sheets(6).Columns("A")).EntireRow.Copy(Sheets(3).Range("F10")) Else: Exit Sub End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi again brilliant forum

i am an excel novice

had some great work done by Wigi and Krishnu in the past

I have about 120 excel files (each has about 1 million rows and between 6 and 30 Columns).

the files are 200mb + so too big to upload

i want a vba macro to delete unwanted rows

is it possible to have a message or input box that i can enter up to 5 words and if any of these words are contained in any rows then i wish to keep these rows only (and if any of these words dont appear i wish to delete the whole row)

the macro will be deleting about 950 thousand rows and keeping about 50 thousand (hopefully).

i can do it very slowly with custom sort but if a macro can be done it will make life easier

Good luck if you fancy the challenge

Paul

For more information about creating custom functions in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type function statement , click Search, and ...

Excel sheets attached to explain.

The original sheet is titled “With code 1.xlsm”. The code is associated with the button “Click me” and once the button is clicked, sheets “1” and “2” are created (click the button to see).

What I would like is for, when sheets “1” and “2” are created, for them to be created with a button “Button 1” linked to another code as shown in file “With code 1 and 2.xlsm” which multiplies the age value by two (click the button to see)

Is this possible?

Thanks thanks ((:

I have IP addresses from cell A1 to cell A200 (this range will change from time to time). Is there a VBA macro to sort the IP addresses?

Thank you.

When adding a password protected Visual Basic for Applications (VBA) project to SourceSafe, you will see one of the following errors. The error you see depends on whether the VBA ...

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :

Account Var. Amount

45000 $105

45005 $50

56100 $25

79008 $125

67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.

Thanks,

Preston

In my dBase3+ and FoxBase+ days, I would create and store a line of code to a variable based on If...Else...EndIf conditions and then execute the code via the variable. For example:

VB:Can this be done in VBA to get around compile errors?County = "KERN" mIndex = "Index On Well_Number For County = "KERN"" Else mIndex="" 'for do nothing EndIf 'the first character "&" means execute code stored in mIndex &mIndexIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

First example is for showing a Userform Modeless-Model which Mac Excel hates (ie, compile error)

VB:The second example is to deactivate the autosave and-or autobackup setting. Mac Excel hates ThisWorkbook.EnableAutoRecovery (compile error: method or data member not found)mUserform = "Userform1.Show" Else mUserform = "Userform1.Show vbModeless" End If &mUserform 'variable to execute the code in mUserformIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Again, I want to save VBA code to a variable based on condition(s) for later execution, and to avoid compiler errorsmSaveSet = "Application.SaveInterval = 0" Else If application.version = "9.0" Then mSaveSet = "Application.AddIns("Autosave Add-in").Installed = False" Else mSaveSet = "ThisWorkbook.EnableAutoRecover = False" End If End If &mSaveSet 'variable to execute the code in mSaveSetIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I plan to have an "Upload Data" button in Total Daily Sales workbook, where once I click on it, it will prompt me to choose a file. Once selected the file (eg Cross Sales Summary), it will extract data from Cross Sales Summary and write it into Total Daily Sales workbook, according to branches. It has to be done on a daily basis except on Saturday and Sunday. Maybe it could extract data into the designated rows according to dates and branches?

Can anyone help me?

Thanks.

I am preparing a spredsheet that on columns, I am inserting numbers as texts (in order to show 0 (zero) before). They are tracking number. So, before create a huge list, those numbers could not be duplicated. My aim is to create a formula or a VBA code to check:

1 - When finalize to insert numbers advise that same number has already been used; or

2 - Create a macro that after pressed the bottom, check the ones that are duplicated.

The easier way would be whenever I type the numbers and changed cell, a popup appear saying that number has already been used.

Any ideas to help me?

I appreciate all attention, since I am not a expert programmer and I am fascinating for VBA on Excel.

If yes what is that

Regards

Vishal

No luck finding an answer? You could always try Google.