Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: excel SaveAs method
I'm loading a tab delimited file {M:\somedir\myfile.txt} into Excel using OLE to manipulate it some & want to save it as an Excel .xls file {M:\somedir\myfile.txt}.
I can get the file to save under the new name, i.e. with the .xls extension but it's still in tab delimited format, NOT Excel's native file format. The following is from a macro I recorded while doing the action I want to automate: ActiveWorkbook.SaveAs FileName:="M:\TMI_Data\Processed\FEB00.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
I can get the following code to save the file and add the filename to the MRU list.
fileXL ="M:\TMI_Data\Processed\FEB00.xls" Awkbk=ObjXL.ActiveWorkbook savefile=Awkbk.SaveAs (fileXL) ; this worksOR
savefile=Awkbk.SaveAs ( fileXL, , , , , ,@True ) ; this works the @True adds the file to the MRU list.But, whenever I try to insert something in the position that I think the fileformat stuff is supposed to go I get 1261 OLE exception errors or 3250 OLE Object error : Problem occurred formatting parameters. I'm wondering if it's a Named parameter ? If I'm understanding the docs correctly (big IF) a named parameter would go after all the positional parameters ? T/F ?
I've tried a lot of permutations & combinations & haven't stumbled up on something that'll work yet.
How does one differentiate between /tell one from the other on Named vice positional parameters ? I've looked in the VBA help & haven't stumbled onto anything.
I've tried the line :
savefile=Awkbk.SaveAs ( fileXL, , , , , , , , , , ::FileFormat = "xlNormal")with 0 to 10 commas for "positional parameters" between the "fileXL" & the "::".
With 0 or 1 comma I get 1261 OLE exception Error & the following entry from wwwbatch.ini
[OLE Exception] Microsoft Excel=Unable to get the SaveAs property of the Workbook classWith 2 to 10 commas I get NO ERRORS, BUT while it saves with an .xls extension it is still Tab delimited.
How can you tell if a parameter is a "Named Parameter" ?
The VBA docs make the stuff all look like "Positional parameters"
If you want to know whether something is a string or a constant, do it in VBA - in this case, something like
var=xlNormalIf it bombs out, it's a string. If it doesn't, it'll return a value for you to plug into your scripts.
expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)
expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)
savefile=Awkbk.SaveAs ( fileXL, -4143 , , , , ,@True )and SHAZAM it works ! It would have been A WHILE before I'd have stumbled on to that. Thanks Again.
Article ID: W14929
File Created: 2001:11:08:12:41:00
Last Updated: 2001:11:08:12:41:00