VBA - Write to Excel File and Name and Save it

[mfu5324]

I primarily write to csv files and have the down pat..... however i am needing to write to a new excel file, name it and then save it. I can create the file, write the data, however i am stumped on how i name and save it. I am proficeint in VBA and when i try to use VBA code it doesnt work. This has been driving me crazy... any help would greatly appreciated.

Excel = CreateObject("Excel.Application");
Excel.Visible = True;
Workbooks = Excel.Workbooks;
Workbooks.Add;
ActiveBook = Excel.ActiveWorkbook;
FileName = "TEST";
Worksheet = Excel.ActiveSheet;
Range = Worksheet.Range("A1", "A1");
Range.Value = 899;
Path = "C:/AMIWEB/CSVData/AllocationFiles/Test.xls";
Excel.Workbooks.Close(SaveChanges:=False);

[mradtke]

This isn't really an AmiBroker question, but are you sure you want to be setting SaveChanges to false in the Excel.Workbooks.Close() call? Also, I think your Path variable needs to be passed into the Close call as well.

Workbook.Close method (Excel)

[Peter2047]

Here is the jscript code for opening and saving a workbook.

var Path = "C:/AMIWEB/CSVData/AllocationFiles/Test.xls";
var ExcelApp = new ActiveXObject("Excel.Application");
ExcelApp.Application.Visible = true;
ExcelApp.Application.DisplayAlerts = false;

var fso = new ActiveXObject("Scripting.FileSystemObject");
if (fso.FileExists(Path)) {
	WScript.echo("file exists.");
	var workbook = ExcelApp.Workbooks.Open(Path);
	var worksheet = workbook.Worksheets("Sheet1");Here 
} else {
	WScript.echo("file not exists.");
	var workbook = ExcelApp.Workbooks.Add;
	var worksheet = workbook.Worksheets("Sheet1");
	workbook.SaveAs(Path);
}

worksheet.Cells(1,1) = 899;
workbook.Save;
workbook.Close;
ExcelApp.Application.Quit();

Some often used code.

var xlUp = -4162;
var xlToLeft = -4159;
//find last occupied row in column A
var ttl_row = worksheet.Cells(worksheet.Rows.Count,1).End(xlUp).Row
//find last occupied column in row 1
var ttl_col = worksheet.Cells(1,worksheet.Columns.Count).End(xlToLeft).Column
//do something while column A is not empty
var i = 1;
while (worksheet.Cells(i,1).text.length > 0) {
	//do something
	i++;
}

[Tomasz]

@mfu5324 - the code you posted is NOT AFL (no := operator in AFL), and is it likely NOT VBA either (don't have semicolons in VBA).

Also if you want to save, just call Save function

ActiveBook.SaveAs(Path);

Also note that paths in AFL need to be written as follows:

Path = "C:\\AMIWEB\\CSVData\\AllocationFiles\\Test.xls";