Solved

saving txt as xls with autofit in VBA

Posted on 2001-07-23
6
674 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 100 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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