Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

From msAccess SaveAs an excel file SILENTLY without opening excel

Access 2010
Excel 2010

I have a dialog box for a person to choose an excel file on the their laptops.

' dialog code

   ' To extract only the filename from the path, you can do the following:
   varFilename = Mid(fDialog.SelectedItems(1), InStrRev(fDialog.SelectedItems(1), "\") + 1, Len(fDialog.SelectedItems(1)))
   varfilePath = Left(fDialog.SelectedItems(1), InStrRev(fDialog.SelectedItems(1), "\"))
   
' store the variables on the form FILE NAME
   Me!FILE_NAME.Value = varFilename ' File Name
   Me!FILE_LOCATION.Value = varfilePath 'FileSelected

Open in new window


Now i need to SaveAs this work book in a new file path and a new file name
but NEVER see excel open.

' HARD CODE STARTING FOLDER PATH ON H: DRIVE
STATIC_FOLDER = "H:\SUPPORT\BookEscalation\"

Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim wsht As Excel.Worksheet
Dim strFileName As String
Dim lastRow As Integer
Set xl = New Excel.Application
xl.DisplayAlerts = False

'Set wbk = xl.Workbooks.Add("c:\Temps\Temp.xls")
CHECKFILE = varfilePath & varFilename

xl.Workbooks.Open "CHECKFILE"
wbk.SaveAs "NEWFOLDER & Temp.xls"
wbk.Close
Set xl = Nothing


THIS IS NOT WORKING ?

Thanks
fordraiders
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fordraiders

ASKER

the reason i have to use the saveAs is because my company encrypts the files on our desktops and then prevents them from being moved to a share drive.  So I wanting to do a silent  Saveas, that will automatically decrypt the file  and i can place the file some where else.
Otherwise I like your appraoch, but will not work here.



Thanks
fordraiders
thanks, I ended up using this anyway.Thanks
Glad you got that to work, but please also understand that we (members of EE) cannot help you to circumvent your company's security policies, no matter how inane they may be :)