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 :)
Who is Participating?
als315Connect With a Mentor Commented:
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
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
     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
Close #FileI
Application.DisplayAlerts = False
ActiveWorkbook.Close True, FO
Application.DisplayAlerts = True
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"
If you provide an example of the source file and destination table columns I can throw together a script real quick for you.

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.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Look at sample
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 :)
In excel you can use an Auto_Open macro or Workbook_Open Sub:
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
if you like to close excel after import.
You can run this template from scheduler
celticianAuthor Commented:
May i run this from a batch?
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
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
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.
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
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
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.
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.
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.