Fordraiders
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.
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
ASKER
Otherwise I like your appraoch, but will not work here.
Thanks
fordraiders