Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to write to an excel file using vbscript? 2

Status
Not open for further replies.

wvdba

IS-IT--Management
Jun 3, 2008
465
US
hi.
i have been given a text file that has 4 columns. i'm supposed to import these 4 items of data into an excel file. for some reason, i can open the text file, identify the data items, but i can't get the excel part to work. can anybody shed some light on:
1) opening a fresh .xls file
2) putting the data in each row - columns 1 to 4...
thanks.
 
Any reason that you can't just open the text file directly with Excel?

When you specify a text file, Excel will walk you through the import process. You can then save it as a .xls file.

There is even a recent post in the VBA forum that gives some code for importing text based files thread707-1620368
 
It has to be done with a script. the object is to make a calendar for any year. the user will enter the year in an input box. the script will create an excel file that will be imported into a database.
thanks.
 
Still sounds like it should be done directly through Excel, but here is some example script code:
Code:
  'Create Excel Object
  set objXL = WScript.CreateObject("Excel.Application")
  'Show it to the user
  objXL.Visible = true
  'Add a workbook
  objXL.WorkBooks.Add()
  objXL.Range("A1").Select
  
  for row = 1 to 4
    for col = 1 to 4
        objXL.Cells(row, col).Value = "some text in row: " & row & ", column: " & col
    next
next
 
thanks much.
it worked ok.
 
does this create an excel file?
what's the file name?
i like to create the file, fill the data and save it.
thanks.
 
objXL.SaveAs "\path\to\file.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Oops, sorry for the typo:
objXL.ActiveWorkbokk.SaveAs "\path\to\file.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
It's not my day:
objXL.ActiveWorkbook.SaveAs "\path\to\file.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
thanks.
it worked very well.
now, i have to import this excel file into access to create a calendar. tbl01 has the following columns:
calendar_date, weekend, holiday, description
they have column headings in excel file as above.
the workbook name is sheet1.
i have a command that i want to make sure it would work:
sql_string = "SELECT * INTO " & table_name & " FROM [Excel 8.0;HDR=Yes;" & _
"Database="& xcl_file_path & "].[fail.RPT$]"
objConnection.Execute sql_string
where would sheet1 go?
 
the problem is that the excel file is created with ms-office 2007 as file.xlsx
while the database is in the access 97-2003 format.
my question is how would i save the file within vbs in excel 97-2003 format?
thanks.
 
objXL.ActiveWorkbook.SaveAs "\path\to\file.xls", 56

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
thanks, phv
i'm going to try it.
cheers.
 
the excel 97-2003 part worked ok.
but, when i tried to import into access-2003, i get this error: can not find installable ISAM
My code looks like this:
sql_string = "SELECT * INTO " & table_name & " FROM [Excel 8.0;HDR=Yes;" & _
"Database=2010_cal.xls].[Sheet1$]"
objConnection.Execute sql_string
it's erroring out.
 
thanks everyone.
the import to access from excel-97 worked ok.
the table is there fine.
my question is that there are two columns that have to be defined as Yes/No items.
how would i accomplish this, please?
right now i have 0 and -1 as text in those fields.
thanks.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top