Excel automation /change drive

[tmreic]

I have a access form in which a command button call the following sub procedure located with in a code module. The module then opens excel and displays the common dialog box using the GetOpenFilename function. Currently, the diaglog box opens to the excels default filepath which is the "my documents" directory. Is there a way to change this path to a different directory from with within the code module?

Thanks,
Tim



Public Sub Bohden()
'On Error GoTo errhand

Dim apExcel As New Excel.Application
Dim xlSheet As Worksheet

Set apExcel = CreateObject("excel.Application&quot
apExcel.Application.WindowState = xlMaximized
apExcel.Visible = True
apExcel.DisplayAlerts = False

Dim dlgAnswer, FileName As String

FileName = apExcel.Application.GetOpenFilename _
("RPT files(*.RPT),*.RPT,rpt02 files(*.rpt02),*.rpt02,all files (*.*),*.*,Text Files (*.TXT), *.txt,CSV Files (*.Csv), *.Csv&quot
If IsNull(FileName) = True Then
MsgBox "No file was selected for processing", vbCritical
Exit Sub

Else
Else

apExcel.Workbooks.OpenText FileName:=FileName _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
'Rows("1:2&quot.Select



Dim shName
shName = apExcel.ActiveSheet.Name

apExcel.Rows("1:2&quot.Select
apExcel.Selection.Delete xlShiftUp
apExcel.Range("A:B&quot.Select
apExcel.Range(Range("A:B&quot, Selection.End(xlDown)).Select
apExcel.Selection.Delete xlShiftToLeft
apExcel.Range("b1:c1&quot.Select
apExcel.Range(Range("b1:c1&quot, Selection.End(xlDown)).Select
apExcel.Selection.Delete xlShiftToLeft








apExcel.DefaultFilePath = "\\Valine\YIELD\tmreic\Public\BohdanPlateWeights\Weights\$Processed Files\"

Dim SaveAsName As String

Dim StrFileName As String
StrFileName = Right(FileName, Len(FileName) - InStr(StrReverse(FileName), ".&quot + 3)
Dim strFileName1
strFileName1 = Left(StrFileName, Len(StrFileName) - 6)

SaveAsName = apExcel.InputBox("Enter the name of the file to save", FileName, strFileName1)

If SaveAsName = "" Then
Exit Sub
Else
apExcel.DefaultFilePath = "\\Valine\YIELD\tmreic\Public\BohdanPlateWeights\Weights\$Processed Files\"

apExcel.ActiveWorkbook.SaveAs FileName:= _
"\\Valine\YIELD\tmreic\Public\BohdanPlateWeights\Weights\$Processed Files\" & SaveAsName & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

apExcel.ActiveWorkbook.Close
apExcel.Quit
Set apExcel = Nothing


MsgBox "The file was saved to the following directory" & Chr(13) & Chr(13) & Chr(10) & _
"\\Valine\YIELD\tmreic\Public\BohdanPlateWeights\Weights\$Processed Files\" & SaveAsName & ".xls"""

End If
MsgBox SaveAsName

End If
Exit Sub
errhand:
MsgBox Err.Description & Err.Number

Exit Sub



End Sub

 

[nat1967]

Hi,

the .GetOpenFilename will open to the where ever the Excel application is defaulted to. I dont believe there is a way to change or set this easily. the GetOpenFileName method doesnt provide a way of specifying the location just the filters.

i am by no means an expert on Excel but I hope this helps.

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[RoyVidar]

Hi!

Check out the .defaultfilepath property of the application object, something like

[tt]apExcel.defaultfilepath=strYourPath[/tt]

Roy-Vidar

 

[RoyVidar]

Oups - posted once again without reading the whole post, sorry. See you're alredy using the property, doesn't it work?

Roy-Vidar