export from excel to text file by batch

Microsoft Windows xp home edition sp2b
April 9, 2010 at 03:52:54
Specs: Windows XP
Dear all,

could you show me plz how to create a batch file can
export data from excel to text file and save this text file in (C:\) drive

plz i need your help


See More: export from excel to text file by batch

Report •


#1
April 9, 2010 at 12:12:49
your script doesn' t need to do much, but your macro does.
here's the script needed, i think:
del /y exported.txt
excel mybook.xls
::-----
In order for the macro to run full-auto (non-interactive, ie: without requiring button-click "enable macros")
you will need to set your security-level to low in the toolsbar:
tools - macros - security
then set the buttons to "security level: low"
(Covering my ass department:
obviously, you ARE compromising security!)
you need this macro to export the code:
Private Sub Workbook_Open()
ActiveWorkbook.SaveAs Filename:="C:\workdir\exported.txt", FileFormat:=xlText, _
CreateBackup:=False

End Sub
the macro cannot close excel, because if it did, you would never be able to access the workbook again! it would close as soon as and every time you tried to open it.
i guess you have to use taskkill to do that unless someone else here has a better solution. I think this whole mess could be handled using vbscript and excel object but i'm not much good with vbscript.

to install the macro, consult msdn:
http://office.microsoft.com/en-us/e...


Report •

#2
April 9, 2010 at 13:21:19
hi nbrane

first, thanks for reply and advice. i really like your answer you give me too much information about excel and macro. unfortunately i have no idea about macro and i don't no how to use this script

Private Sub Workbook_Open()
ActiveWorkbook.SaveAs Filename:="C:\workdir\exported.txt", FileFormat:=xlText, _
CreateBackup:=False

End Sub

if you help more in your script and how i can use this script i really appreciated.
I agree with you with this command taskkill to close every thing and i see it's a best and easiest way to close any thing

plz nbrane could u guide me what should i do


Report •

#3
April 11, 2010 at 04:26:54
plz friends i need help here

Report •

Related Solutions

#4
April 11, 2010 at 12:45:56
Call me stupid, but batch-files dont run in Excel (not with data entered in a datasheet, that is) ... you need a Macro, and that is a language for which you need to find an expert.

Subcategory should be : Excel Macro


Report •

#5
April 13, 2010 at 15:02:24
::------ edit ------- edit ----- fixed goofs!
@echo off & setlocal
if exist exl.vbs goto :alrdydon
:: might need to change this line
set xls=c:\excel\mycalendar.xls
:: change the following to suit your preference
:: (ie: where you want the exported text to go)
set outp=c:\doc\test.txt
> exl.vbs echo Dim xl
>> exl.vbs echo Set xl = WScript.CreateObject("Excel.Application")
:: change to false if you want it running in stealth-mode
>> exl.vbs echo xl.application.Visible = TRUE
:: this disables the overwrite-safety dialogue
>> exl.vbs echo xl.application.displayalerts=false
>> exl.vbs echo set ac=xl.WorkBooks.open("%xls%")
>> exl.vbs echo ac.SaveAs "%outp%",-4158
>> exl.vbs echo xl.application.quit
:alrdydon
wscript exl.vbs

Report •

#6
April 14, 2010 at 00:04:12
hi nbrane where are you !!

welcome back, i test your code, i create a new folder in (C:\excel) drive and rename my excle file with test. after that, i create a new text file and past your code in this file and save it with .bat file. After double click, i see there is 2 new (.vbs) file was automatically created. I double clicked in the (elx.vbs) file this flie trying to open my excel file and then i get an error massage

Script: C:\excel\elx.vbs
Line: 4
Char: 1
Error: 'C:\excel\mycalendar.xlc' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted
Code: 800A03EC
Source: Microsoft Office Excel

plz more help and advice


Report •

#7
April 14, 2010 at 09:30:06
normally, excel uses the extension ".xls". have you checked the spelling, location etc? ("'C:\excel\mycalendar.xlc' could not be found. Check the spelling of the file name, and verify that the file location is correct. ")
f/e:
c:\excel\mycalendar.xls


Report •

#8
April 14, 2010 at 10:19:39
hi nbrane

i checked all of these points many times

- file extension
- file name
- file path

i checked any thing
put it's not work


Report •

#9
April 14, 2010 at 10:47:08
i did see an error in my script, not sure if it's relevant though:
> exl.vbs echo Dim xl
should be:
> elx.vbs echo dim xl
(that's why it created two .vbs files instead of just one.)
i will "repair" the script posted above.
you will need to:
a) delete both .vbs files to clear the slate
b) reload the fixed script and
c) make any local changes needed for your use and system environment.
i will fix line 3 to reflect your file, but using ".xls" extension. if that is wrong, just change it in the vb script.
sorry for the inconvenience. my typeing skills absolutely suck hence the dislexed letters.

Report •

#10
April 16, 2010 at 06:44:44
hi nbrane

is it possible that only small and capital letter make different
> exl.vbs echo Dim xl
> elx.vbs echo dim xl

i made this change u mentioned but there is no different it's
same error same two vbs files .

nbrane don't worry about your typing skills i can understand you

could u also provide me with this change u mentioned

plz nbrane i still stuck here


Report •

#11
April 19, 2010 at 11:33:18
Excel cannot open C:\excel\mycalendar.xlc. Either
A) The file name and or path is incorrect.
B) You do not have read access to the file
C) You're using Office 2007, which removed support for .XLC files.

Report •

#12
April 19, 2010 at 12:20:53
hi Razor2.3

how r u , I see you have many comments here.

So plz if you have more info about my issue share it with us


Report •

#13
April 19, 2010 at 12:22:53

plz Razor2.3 i still stucked


Report •

#14
April 19, 2010 at 18:36:39
hello, i just noticed you said you renamed your file:
"i test your code, i create a new folder in (C:\excel) drive and rename my excle file with test. after that, i create a new text file and past your code in this file and save it with .bat"
did you rename the excel file, or just copy it? If you renamed mycalendar.xlc to test.xlc, you might have caused it to fail since it was looking for mycalendar.
Everything worked on my end, so it's got to be something like Razor said - and i'm sorry i can't help with that since that is purely a local issue needing to be resolved.

Report •

#15
April 19, 2010 at 22:33:58
hi nbrane

I have a good solution for this situation , could access my PC

and show me what exactly u did in your PC.

if u accepted I really appreciated


Report •

#16
April 19, 2010 at 22:34:55
plz this is my email for contacting XXXXXXXXXXX@hotmail.com

edited by moderator: Email addresses removed. -Razor2.3


Report •


Ask Question