Solved

Format Excel Columns as Text from MS Access using VBA

Posted on 2013-10-25
5
7,114 Views
Last Modified: 2013-10-27
I need to format two Excel columns as text from MS Access using VBA. Any thoughts.
0
Comment
Question by:shieldsco
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39601875
You need to open the Excel Workbook and then format two columns?

The following will open PATH/Name.xls and format Columns A through B as Text on the first Sheet:

    Set objApp = CreateObject("Excel.Application")
        objApp.Visible = True
        Set wb = objApp.Workbooks.Open("PATH/Name.xls", True, False)
    
    wb.Sheets(1).Range("A:B").NumberFormat = "@"

Open in new window


Change the PATH/Name.xls to your Excel File, the number of the Sheet, then the Column(s).
0
 

Author Comment

by:shieldsco
ID: 39603761
Works good but how do I save the workbook and close the application
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39603935
    Set objApp = CreateObject("Excel.Application")
        objApp.Visible = True
        Set wb = objApp.Workbooks.Open("PATH/Name.xls", True, False)
    
    wb.Sheets(1).Range("A:B").NumberFormat = "@"
    
    wb.SaveAs "PATH/Name.xls"
    wb.Close
    Set objApp = Nothing

Open in new window

0
 

Author Comment

by:shieldsco
ID: 39604226
How do I close the Excel application?

Private Sub Command0_Click()
 Set objApp = CreateObject("Excel.Application")
        objApp.Visible = True
        Set wb = objApp.Workbooks.Open("C:\Users\ShieldsCo\Documents\Change Text.xlsx", True, False)
   
    wb.Sheets(1).Range("A:B").NumberFormat = "@"
    wb.Save
    wb.Close
 
    Set objApp = Nothing
   
End Sub
0
 

Author Closing Comment

by:shieldsco
ID: 39604262
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now