Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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