Opening non-Excel file via VBA 1

[Smoothcat74]

Hi all,

I am using VBA to open a file in Microsoft Excel in an effort to save it in 2007 format. The file being opened is a plain .txt file, but VBA returns an error because the file isn't a .xls or .xlsx (I'm assuming.) Does anyone know an effective workaround for this issue?

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(ExcelPath & Format(Date, "mmmdd_yy") & ".txt")
Xl.Visible = True
XlBook.Windows(1).Visible = True

XlBook.SaveAs ExcelPath & Format(Date, "mmmdd_yy")

 

[SkipVought]

I would not use the Open method to open a text file in Excel.

Rather IMPORT into Excel.

Figure out exactly how to do it on the sheet. Then macro record EDITING the querytable that imports the data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothcat74]

Good thinking SkipVought. I'll give that a try and see how it comes out.

Thanks very much!

 

[Smoothcat74]

Hmmmmm... the only problem with this route is that I have to manually define the text-to-columns. When the file is just opened for some reason it takes care of the formatting and drops everything into properly aligned cells.

 

[SkipVought]

"The file being opened is a plain .txt file"

Now you're saying that the file is something else: maybe a TAB or COMMA delimited text file?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Have a look at the Open or OpenText methods of the Workbooks collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

The reason I lean toward IMPORT rather than OPEN methods is that you have full control over the parsing column data types, which is essential when the file contains certain kinds of data, which Excel may interpret in ways you may not want.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}