• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

run excel function from access

Hi Experts, Need some help with running the following excel function from access vba code.

Sub FormatChange()
    Range("K1:K2000").Select
    Selection.NumberFormat = "yyyymmdd"
End Sub
0
needhelpfast569
Asked:
needhelpfast569
  • 5
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you have to open the excel file first

Sub FormatChange()
dim xlObj as Object, xlFile as string
 xlFile="c:\somefolder\myExcel.xls"
set xlObj=createobject("excel.application")
      xlObj.workbooks.open xlFile
    with xlObj
               .worksheets("sheet1").activate
               .Range("K1:K2000").Select
               .Selection.NumberFormat = "yyyymmdd"
                .activeworkbook.save
     end with

     xlObj.quit

End Sub
0
 
needhelpfast569Author Commented:
hi capricorn1, That worked great cleared up the problem. But is there anyway to make it so it does not ask the user to save the changes and overwrite the existing file?

Thanks Rick
0
 
Rey Obrero (Capricorn1)Commented:
try this


Sub FormatChange()
dim xlObj as Object, xlFile as string
 xlFile="c:\somefolder\myExcel.xls"
set xlObj=createobject("excel.application")
      xlObj.workbooks.open xlFile
    with xlObj
               .worksheets("sheet1").activate
               .Range("K1:K2000").Select
               .Selection.NumberFormat = "yyyymmdd"

               .displayalerts=false  'ADD this line

                .activeworkbook.save
     end with

     xlObj.quit

End Sub
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
needhelpfast569Author Commented:
Perfecxt thank you very much
0
 
Rey Obrero (Capricorn1)Commented:
what? you are accepting your own post as the solution to your problem..
0
 
needhelpfast569Author Commented:
completed the task
0
 
Rey Obrero (Capricorn1)Commented:
needhelpfast569,


it is not right that you accept your own post and forget about the expert that helped you resolve your problem..



you must give credit to whoever helped you.

..
0
 
needhelpfast569Author Commented:
Sorry as far as I know I gave you the whole 500 points and all the credit.  You gave me the perfect solution to the problem. If I screwed up how can I correct it?
0
 
Rey Obrero (Capricorn1)Commented:
click the Request Attention just below your original post, and ask to reopen the thread so you can correct the mistake and select the post at

http:#a39199220 

as the accepted solution
0
 
needhelpfast569Author Commented:
Hope this fixs the problem thanks for your help.
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

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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