In essence this my project :

I have a .log file that I need to convert structuraly to make it
usefull in FileMaker.

- open the logfile
- In the .log file there is a character that needs to be replaced with
another, to be able to convert it more easely into colums.
- Delete colums the Second and Forth colum (in excel since the shift
places it is first B, than C)
- Then those colums needs to be transposed in order to be able to
import them into FileMaker.
- save the logfile

I can do this in Excel but I want to skip the use of Excel. I only like
to use FM because it is going to be a runtime application. My costumers
wont have a 'full version' of FM

running FM pro 8.5 on Win XP.

Kind regards and Tx in advance !!!!

___________________________________
Option Explicit
Sub ReplaceAndTranspose()


Dim FromChars As Variant
Dim ToChars As Variant
Dim iCtr As Long


FromChars = Array(Chr(28))
ToChars = Array(Chr(124))


If UBound(FromChars) <> UBound(ToChars) Then
MsgBox "design error--make from/to match"
Exit Sub
End If


For iCtr = LBound(FromChars) To UBound(FromChars)
ActiveSheet.Cells.replace What:=FromChars(iCtr), _
Replacement:=ToChars(iCtr), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A1:B200").Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWorkbook.Save

End Sub

I can't help with visual basic, but have other suggestions.

1) Filemaker can be told to ignore columns when importing, so there is really no
need to delete columns first. Just specify the macro to import the columns you
want and ignore the others.

2) Filemaker can insert any position of the import file into any column specified,
so there is no need to rearrange columns before importing.

3) Replacem (http://www.fitsoftware.com/replace/) is free ware and can be called
silently from a batch file in your import macro to do a find and replace. Unless
the import file is enormous (like several megabytes) it will likely run so fast
that they won't even notice a pause.

Kent

chapeau_melon wrote: