Inserting txt file into MS excel file from script?

Is there anyway i can execue any script by just one click that will insert a txt file into an already existant MS excel file?

i need to do this routine every day, i need to insert the content of certain txt file into a quite simple ms excel template, in the second row of the first column.

I thought there might be some way of automatizing this absurd task.

thanks in advance :)
LVL 1
celticianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ziplerCommented:
If you provide an example of the source file and destination table columns I can throw together a script real quick for you.

 Thanks,
0
celticianAuthor Commented:
I attach three files:

The txt source
The xlsx destination (need to be insterted from secon row first column
And a capture with the data separators that need to be removed whe importing from the txt

Id like to know how can this be achieve rather than just the script (which is good of course :) ) but i want to learn myself as i dont find a lot of documentation on this issue over the internet.

ps: all private data has been replaced with random characters, just leaving the data structures.
myfile.xlsx
txtfile.TXT
data-import.png
0
als315Commented:
Look at sample
myfile.xlsm
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

celticianAuthor Commented:
How do i invoke that from a batch file and save it?

if i have to open myself the excel document, i'm still in the same situation :)
0
als315Commented:
In excel you can use an Auto_Open macro or Workbook_Open Sub:
http://office.microsoft.com/en-us/excel-help/running-a-macro-when-excel-starts-HA001034628.aspx
place code from my sample to sub. You can add to the end:
ActiveWorkbook.SaveAs Filename:="\\unc_file_name_with_path.xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        True, CreateBackup:=FalseActiveWindow.Close
Application.Quit
if you like to close excel after import.
You can run this template from scheduler
0
celticianAuthor Commented:
May i run this from a batch?
0
als315Commented:
Yes. You should have excel on computer, where it will be started. Look at sample. Place file to c:\tmp folder and run it from batch. You can open file with Shift pressed and auto_open macro execution will be stopped. Look at code in Module1
Book1.xlsm
0
celticianAuthor Commented:
When opening the file for the second time, i get a first error telling me that such file already exists in the tmp folder and afterwards i get:

Run time error 1004

method save as object  workbook failed

When i delete it and execute again... im able to get to the inner code where i see:

Sub Auto_Open()
'
ActiveWorkbook.SaveAs Filename:="c:\tmp\test1.xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        True, CreateBackup:=False
'ActiveWindow.Close
Application.Quit
End Sub

Where in this code is there any reference pointing to my files so i can replace names and see how it works?

I really don't know how to edit this and make it work/read my excel and txt files.
0
als315Commented:
Look at sample. All code was combined in one auto_open sub. You may get error during first open - correct path to text and out files
myfile.xlsm
0
celticianAuthor Commented:
How do i edit this so i can change header names? when i open the file for the second time it closes itself all the time. i tried to do it when opening for the first time the file so i would return a debug error but when saving it it would save without the macros it said.

also, the output name changes everyday, its not static, its filename_DD_MM_YY.xlsm
0
als315Commented:
You can suppress execution of Auto_Open macro if Shift key will be pressed during file opening. Auto_Open sub code:
Sub Auto_Open()
Dim FI As String
Dim FO As String
Dim R As Long
Dim FileI As Integer
Dim Str As String, C As String
R = 2
FI = "c:\tmp\txtfile.txt" 'Source test file
FO = "c:\tmp\outfile.xlsx" 'Saved xls file
If Dir(FO) <> "" Then Kill FO 'Delete resulting file if exists

FileI = FreeFile
ActiveSheet.Copy
Open FI For Input As #FileI
Do Until EOF(FileI)
     Str = ""
     C = Chr(1)
     Do While Asc(C) <> &HA
        If Asc(C) >= 32 Then Str = Str & C
        C = Input(1, #FileI)
        If EOF(FileI) Then Exit Do
     Loop
     If Len(Str) > 0 Then
        ActiveSheet.Cells(R, 1).NumberFormat = "@"
        ActiveSheet.Cells(R, 1) = Trim(Mid(Str, 1, 15))
        ActiveSheet.Cells(R, 2) = Trim(Mid(Str, 16, 2))
        ActiveSheet.Cells(R, 3) = Trim(Mid(Str, 18, 2))
        ActiveSheet.Cells(R, 4) = Trim(Mid(Str, 20, 3))
        ActiveSheet.Cells(R, 5) = Trim(Mid(Str, 23, 5))
        ActiveSheet.Cells(R, 6) = Trim(Mid(Str, 28, 5))
        ActiveSheet.Cells(R, 7) = Trim(Mid(Str, 33, 27))
        ActiveSheet.Cells(R, 8) = Trim(Mid(Str, 60, 2))
        ActiveSheet.Cells(R, 9) = Trim(Mid(Str, 62))
        R = R + 1
    End If
Loop
Close #FileI
Application.DisplayAlerts = False
ActiveWorkbook.Close True, FO
Application.DisplayAlerts = True
Application.Quit
End Sub

Open in new window


You can change file name (FO) to something like:
FO = "c:\tmp\filename" & Format(Date, "dd_mm_yy") & ".xlsx"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
celticianAuthor Commented:
Ok, it seems to be improving, but i had to edit the code just right after executing and getting an error so i can debug, i can get this by deleting the source file too, but if the file is fine and the script works, there's no way of stoping the executing not even pressing shift+double click.

I need to change the source template, just a few things.
0
als315Commented:
Don't release Shift until file will be opened.
Other way - temporarily increase safety level in Excel to "macros are disabled", edit code and set it back.
0
celticianAuthor Commented:
Oh ok, i could edit it by replacing the source file and provoking a debug, but it wouldn't stop executing by pressing Shift.

Anyway i made finally the script working. thank you very much, i can use it and i'll try to understand some parts of the code :)

well done!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.