0

I've a problem which I'm trying to solve at the moment. It concerns converting formats in an excel file, which drives me crazy.

Import raw data from excel into another excel

Now I've found a way where I'm optimistically that it could solve the issues. To "fix" it I'm using the functionality "text in rows" -> as VBA recorded as 

   Selection.TextToColumns Destination:=range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

I've learned, that it is possible to use this code to fix one row but not many at once. Now I'd like to know, how this code can be improved, that it fix a range ("A7", "DR621") at once.

I'd be very happy if someone could assist me and point me in the right direction.

Thanks in advance Arthur

EDIT: sorting error as screenshot

http://www.xup.in/dl,78038715/date_wrong_sorting.jpg/

Community
  • 1
  • 1
Arthur
  • 9
  • 3
  • the problem here is that when you convert text to columns, it will overwrite whatever content is in the columns next to your text. If you are confident that there are enough blank columns after the imported data then you could loop around the code above, changing "A1" to whatever columns needed text-to-columns'ing. Looking at your previous posts, it's possible that your incoming data is tab delimited with a *.xls extension - which is why it's being imported wierdly. – RowanC Jul 24 '13 at 09:17
  • @RowanC - Thanks! RE: tab delimited - yes probably. The mysql export is done via an php script. Output is excel (*.xlsx). That raw data excel I try to import in a sheet which contains a few simple macros to make sort up and down and navigation a bit easier for unexperienced users. – Arthur Jul 24 '13 at 09:36
  • If you could be more specific about "fix" you want, we could also be more helpfull. – LS_ᴅᴇᴠ Jul 24 '13 at 09:38
  • Hello @LS_dev - with "fixing" problem I mean to convert text into values. I believe, that this would solve the other problem I have with sorting. – Arthur Jul 24 '13 at 10:56
  • Putting new issue on top of solved one within same topic is not a good idea. You can open new thread/question instead. – revoua Jul 24 '13 at 15:10

1 Answers1

0

How to parse data using the Text To Columns command in Excel

The range that you convert may contain many rows, but you can only convert one column of data at a time.

You can loop columns something like this (exampled offset is 5 columns on the same sheet): 

For Each c In Selection.Columns
   c.TextToColumns Destination:=Cells(c.Row, c.Column + 5), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
Next
revoua
  • 2,044
  • 1
  • 21
  • 28
  • Thanks revoua for the fast response! Would it be possible to automate this with any kind of loop? – Arthur Jul 24 '13 at 09:23