Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

saving txt as xls with autofit in VBA

Posted on 2001-07-23
6
Medium Priority
?
677 Views
Last Modified: 2007-12-19
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
Comment
Question by:arichee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
xSinbad earned 300 total points
ID: 6310465
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
 

Author Comment

by:arichee
ID: 6310527
I'm getting a VB compile error on Excel.Workbooks.Close SaveChanges:=True.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6310544
Sorry;

Workbooks("test.xls").Close SaveChanges:=True
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Expert Comment

by:nfernand
ID: 6312709
To stop Excel from asking yes/no do:
application.displayalerts = false
0
 

Author Comment

by:arichee
ID: 6313418
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
 

Author Comment

by:arichee
ID: 6314782
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question