Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

saving txt as xls with autofit in VBA

The below is an attempt to save a tab-delimited text file into an Excel spreadsheet, with all columns auto-fitted.  The AutoFit doesn't work, and I keep getting prompted to save changes, which won't cut it in a batch environment.  Any suggestions?

Dim Excel As New Excel.Application  
Excel.Workbooks.Open "c:\test.txt"
Excel.Cells.Select
Excel.Selection.Columns.AutoFit
Excel.SaveWorkspace "c:\test.xls"
Excel.Workbooks.Close
Excel.Quit
0
arichee
Asked:
arichee
  • 3
  • 2
1 Solution
 
xSinbadCommented:
Try this;

Dim Excel As New Excel.Application  
Excel.Workbooks.Open "c:\test.txt"

Excel.Range("A1").Select

Excel.Selection.Columns.AutoFit
Excel.SaveWorkspace "c:\test.xls"

Excel.Workbooks.Close SaveChanges:=True
Excel.Quit


Cheers
Marcus
0
 
aricheeAuthor Commented:
I'm getting a VB compile error on Excel.Workbooks.Close SaveChanges:=True.
0
 
xSinbadCommented:
Sorry;

Workbooks("test.xls").Close SaveChanges:=True
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
nfernandCommented:
To stop Excel from asking yes/no do:
application.displayalerts = false
0
 
aricheeAuthor Commented:
Now I'm getting a "Run-time error '9': subscript out of range."

Dim Excel As New Excel.Application
Excel.Application.DisplayAlerts = False
Excel.Workbooks.Open "c:\test.txt"
Excel.Range("A1").Select
Excel.Selection.Columns.AutoFit
Excel.Workbooks("c:\test.xls").Close SaveChanges:=True
Excel.Quit
0
 
aricheeAuthor Commented:
Your answer was the closest to the solution below.  Thanks.

Dim Excel As New Excel.Application
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
 
Excel.Workbooks.OpenText "c:\file.txt"
 
Set oSheet = Excel.ActiveSheet
Set oRng = oSheet.Range("A1", "Z1")
oRng.EntireColumn.AutoFit
Excel.ActiveWorkbook.RefreshAll
 
Excel.ActiveWorkbook.SaveAs "c:\test.xls", FileFormat:=xlNormal
Excel.Quit
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now