Avatar of celtician
celticianFlag for American Samoa asked on

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 :)
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
celtician

8/22/2022 - Mon
zipler

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

 Thanks,
ASKER
celtician

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
als315

Look at sample
myfile.xlsm
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
celtician

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 :)
als315

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
ASKER
celtician

May i run this from a batch?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
als315

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
ASKER
celtician

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.
als315

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
celtician

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
ASKER CERTIFIED SOLUTION
als315

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
celtician

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.
als315

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
celtician

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!