CODEWITHSUNDEEP
vbaexcel
Cornelis
Cornelis

Reputation: 435

Get data from .txt file

I'm in a struggle with a MS Excel(2010) VBA problem. I'd like to be using a VBA macro to extract data form a text file. I've been using the code below which works but I'm looking for a more universally applicable code.

ActiveCell.Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Mid(text, G10 + 4, 3)

The code finally will be reading measurement reports from a CNC machine with a touch probe programmed to produce this automatically. This CNC machine delivers the measurement data in a text file. The measurement reports have about the following format:

G10:299mm
G20:45mm 
G30:70mm
G40:100,02mm
G50
G60
G70
G80
G90
G100
........etc.

The drawback/disadvantage of this code is not being stable and universal. When for instance measure "G40" would appear to be just a little smaller, say 99,97mm is already had one number less.

And if possible, I'd like the code to be flexible enough to run on different (lengths) of measure reports. (Which could easily be achieved by running a loop if the code for reading the measures is suitable).

Would be nice if anyone would know a more suitable alternative for this part of the code.

ActiveCell.Select
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Mid(text, G10 + 4, 3)

Upvotes: 0

Views: 99

Answers (1)

L42
L42

Reputation: 19727

I'll just directly answer your question for the alternative.
Below will do just that.

ActiveCell.Value = Split(text, ":")(1) 'returns 100,02mm
ActiveCell.Value = Replace(Split(text, ":")(1), "mm", "") 'returns 100,02

If you have other concerns more than that, edit your question or leave a comment.

Upvotes: 1

Related Questions