0

I am simply opening a file, deleting two lines and changing the contents of cell a1 and then trying to save the excel file as a tab delimited text file. I have tried so many ways but everything is giving me a compilation reading "expected statement" error wherever there is a colon (:).

 Set ExcelObject = CreateObject("Excel.Application")
 ExcelObject.visible = True
 ExcelObject.Workbooks.Open"c:\snowfall.xlsx",Default, False
 ExcelObject.sheets(1).Rows("1:2").Delete
 ExcelObject.Sheets(1).Cells(1,1).value = "testing write function"
 ExcelObject.SaveAs Filename:=Activesheet.name, FileFormat:=xlText, _

the only line that has an error is the last one: 

ExcelObject.SaveAs Filename:=Activesheet.name, FileFormat:=xlText, _

I have been banging my head against the wall for the last three days trying to get this to work :/

Comintern
  • 21,855
  • 5
  • 33
  • 80
Sonican
  • 27
  • 1
  • 2
  • you have an _ at the end of that line so there is additional options set that you haven't shown......or that is what may be causing the issue.... – Sorceri Aug 04 '16 at 17:16
  • 1
    `ExcelObject.SaveAs` is wrong, you save workbooks not the application itself. Also show the full code for SaveAs.... the issue might be there. – cyboashu Aug 04 '16 at 17:20
  • the short answer is replace `ExcelObject.SaveAs` with `ExcelObject.Sheets(1).SaveAs` – Slai Aug 04 '16 at 18:31

1 Answers1

0

You have several errors.

  1. As mentioned in the comments, you save Workbooks, not the Excel.Application object. The calls to .Sheets need to reference the Workbook as well. The .Open method returns a reference to the Workbook - grab that for use below.
  2. VBScript knows nothing about the built in Excel constants. You need to supply the actual values that they represent in the enumeration. For example, xlText is -4158.
  3. VBScript doesn't support named argument syntax. You'll need to pass them explicitly.
  4. ActiveSheet.Name is going to return snowfall.xlsx. You'll need to save the file with a .txt extension.

The end result should look more like this:

 Set ExcelObject = CreateObject("Excel.Application")
 ExcelObject.Visible = True
 Set wb = ExcelObject.Workbooks.Open("c:\snowfall.xlsx")
 wb.Sheets(1).Rows("1:2").Delete
 wb.Sheets(1).Cells(1, 1).Value = "testing write function"
 wb.SaveAs "c:\snowfall.txt", -4158
Comintern
  • 21,855
  • 5
  • 33
  • 80