Import TXT file and convert

[HankD]

Hi,
I am new to this forum.

I need to import txt files to excel and format them
with three different column formats.
I would like to have a scheme so these formatting
macros can be saved and used again.
Is there a plug-in or a program that can help me?
The text importer can't save the formatted schemes?
I am not used to programming or macros or the like.
A downloadable simple program maybe...?

Best
---Hank

 

[HankD]

Hi again,
I tried to understand this:
ChDir "Z:\Management\Customer Relations\Complaint Log"
Workbooks.Open Filename:= _
"Z:\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"

That last xls file, what does that really mean?
Would this formula let me open one new file (at choice)
but what does the end file mean? Would it save it there
as an xls file?
Dont know if this makes me point somewhere i shouldn't...?
Can't get this one to work. All my other macros worked
like a dream - and I edited the macro to find the next file
when I moved to the next operation.
Thanks
----Hank

 

[MarkAndrews]

Hi again,
I tried to understand this:
ChDir "Z:\Management\Customer Relations\Complaint Log"
Workbooks.Open Filename:= _
"Z:\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"

That last xls file, what does that really mean?
Would this formula let me open one new file (at choice)
but what does the end file mean? Would it save it there
as an xls file?
Dont know if this makes me point somewhere i shouldn't...?
Can't get this one to work. All my other macros worked
like a dream - and I edited the macro to find the next file
when I moved to the next operation.
Thanks
----Hank

Hi there, my initial code explained

ChDir "Z:\Management\Customer Relations\Complaint Log"
(Above identifies the file directory)

Workbooks.Open Filename:= _
"Z:\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls"
(Above identifies both the directory & filename to open, Workbooks.Open Filename:= is the command to open the named workbook

Hope this helps you more?

 

[HankD]

Hi again!
Do I understand it right that this macro would make it possible for me to
open a New file when I use it, not only the one file that I had pointed out
in my first macro? In your second line you write "Complaint Log 2006.xls"
which is one specific file, right? I feel thick but I still don't get it...sorry...
Best/Hank

 

[MarkAndrews]

Hi there

The line of code i gave you looks at a specific named file in the stipulated directory

Z:\Management\Customer Relations\Complaint Log\Complaint Log 2006.xls

If you were to save your Text file as the same file each time and in the same location (save over it with a new version) the macro will only look at this file & open this file when running the macro

I suspect that there is a problem with your code, as the code i supplied is a working code in one of my workbooks.

Its difficult to ascertain the problem

I have added a wait into your code, after the file has been opened - see if this works?

Sub Makro4() 
' 
' Makro4 Makro 
' Makrot inspelat 2006-09-25 av Henry Denander 
' 

' 
Application.ScreenUpdating = False 
ChDir "C:\Documents and Settings\Henry Denander\Mina dokument\AA UPGRADE\STIM avräkningar\JAN JUNI 2006\ncb juni 2006" 
Workbooks.Open Filename:= _ 
"C:\Documents and Settings\Henry Denander\Mina dokument\AA UPGRADE\STIM avräkningar\JAN JUNI 2006\ncb juni 2006.xls" 
newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 10
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _ 
Array(Array(0, 1), Array(1, 1), Array(5, 1), Array(14, 1), Array(32, 1), Array(62, 1), _ 
Array(92, 1), Array(93, 1), Array(95, 1), Array(115, 1), Array(125, 1), Array(126, 1), _ 
Array(129, 1), Array(132, 1), Array(142, 1), Array(152, 1), Array(161, 1), Array(163, 1), _ 
Array(165, 1), Array(175, 1), Array(185, 1), Array(193, 1), Array(202, 1), Array(232, 1), _ 
Array(262, 1), Array(292, 1), Array(295, 1), Array(304, 1), Array(334, 1), Array(337, 1), _ 
Array(346, 1), Array(376, 1), Array(379, 1), Array(388, 1), Array(393, 1), Array(397, 1), _ 
Array(427, 1), Array(428, 1), Array(429, 1), Array(433, 1), Array(443, 1), Array(473, 1), _ 
Array(482, 1), Array(512, 1), Array(521, 1), Array(530, 1), Array(532, 1), Array(562, 1), _ 
Array(571, 1), Array(601, 1), Array(610, 1), Array(640, 1), Array(645, 1), Array(655, 1), _ 
Array(658, 1), Array(663, 1), Array(673, 1), Array(677, 1), Array(692, 1), Array(707, 1), _ 
Array(716, 1), Array(717, 1), Array(720, 1)), TrailingMinusNumbers:=True Application.ScreenUpdating = True 
End Sub

 

[njimack]

Could the problems be to do with the fact that your arrays are all over the place?

Below is code I use to open text files via Excel. It makes use of procedures I found at http://j-walk.com/ss/excel/tips/tip54.htm to make sure that the path and filename exist.

You should be able to adapt it very easily - simply change the variables FileLocation, OpeningFileName and obviously, the array relating to the text file

Private Function Path() As Boolean
'   Returns TRUE if the path exists
    Dim x As String
    On Error Resume Next
    x = GetAttr(FileLocation) And 0
    If Err = 0 Then PathExists = True _
      Else PathExists = False
End Function

Private Function File() As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(OpeningFileName)
    If x <> "" Then FileExists = True _
        Else FileExists = False
End Function
    
Sub Update()

'Change this to the correct path - make sure you leave the forward slash (\) at the end
FileLocation = "C:\TEMP\"

'Need to ensure the user saves the file using this file name
OpeningFileName = "SMBVAL2.exp"

OpeningFileName = FileLocation & OpeningFileName

'Confirm path and file exist
Call Path
If PathExists = False Then
Call MsgBox(FileLocation & " doesn't exist", vbOKOnly + vbCritical, "Process terminated")
Exit Sub
End If

Call File
If FileExists = False Then
Call MsgBox(OpeningFileName & " doesn't exist", vbOKOnly + vbCritical, "Process terminated")
Exit Sub
End If

Application.StatusBar = "Opening text file..."
' Open text file with correct field formats
' Note in Array the 2nd number is 1 for General, 2 for text and 4 for date
    Workbooks.OpenText Filename:=OpeningFileName, Origin:=xlWindows _
    , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
    Array(8, 4), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
    Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), _
    Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
    Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), _
    Array(28, 1), Array(29, 1), Array(30, 1))

Application.StatusBar = False
End Sub