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.
Course Of The Month
1 day, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
saving txt as xls with autofit in VBA
Posted on 2001-07-23
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.Au
Excel.SaveWorkspace "c:\test.xls"
Excel.Workbooks.Close
Excel.Quit
Dim Excel As New Excel.Application
Excel.Workbooks.Open "c:\test.txt"
Excel.Cells.Select
Excel.Selection.Columns.Au
Excel.SaveWorkspace "c:\test.xls"
Excel.Workbooks.Close
Excel.Quit
[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
Try this;
Dim Excel As New Excel.Application
Excel.Workbooks.Open "c:\test.txt"
Excel.Range("A1").Select
Excel.Selection.Columns.Au
Excel.SaveWorkspace "c:\test.xls"
Excel.Workbooks.Close SaveChanges:=True
Excel.Quit
Cheers
Marcus
Dim Excel As New Excel.Application
Excel.Workbooks.Open "c:\test.txt"
Excel.Range("A1").Select
Excel.Selection.Columns.Au
Excel.SaveWorkspace "c:\test.xls"
Excel.Workbooks.Close SaveChanges:=True
Excel.Quit
Cheers
Marcus
Now I'm getting a "Run-time error '9': subscript out of range."
Dim Excel As New Excel.Application
Excel.Application.DisplayA
Excel.Workbooks.Open "c:\test.txt"
Excel.Range("A1").Select
Excel.Selection.Columns.Au
Excel.Workbooks("c:\test.x
Excel.Quit
Dim Excel As New Excel.Application
Excel.Application.DisplayA
Excel.Workbooks.Open "c:\test.txt"
Excel.Range("A1").Select
Excel.Selection.Columns.Au
Excel.Workbooks("c:\test.x
Excel.Quit
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.Refre
Excel.ActiveWorkbook.SaveA
Excel.Quit
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.Refre
Excel.ActiveWorkbook.SaveA
Excel.Quit
Featured Post
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…
Suggested Courses
Course of the Month1 day, 17 hours left to enroll
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.