By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Open/Save Excel Sheet - Force it to save to a specific directory

P: 7
Hi all,

As with all database developments I keep getting more and more requirements piled on me and was hoping that someone could help me out with this one as it is outside my knowledge!!!

One of my forms shows project information and has the capability of browsing and attaching files to the current record (works fine). There is a command button that opens up a template excel file that we use in the office, this is were I get lost!

The excel form opens up fine but I have been asked if I can automatically force the excel file to save to the correct place (I have a filing system that attached documents are saved to) once the user has finished with it.

I have absolutely no idea if this is possible as I have never tried anything like this before!

Any help, even if it is just a confirmation that this is beyond the capability of access, is most appreciated!!

Cheers

Phil
Jun 13 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi all,

As with all database developments I keep getting more and more requirements piled on me and was hoping that someone could help me out with this one as it is outside my knowledge!!!

One of my forms shows project information and has the capability of browsing and attaching files to the current record (works fine). There is a command button that opens up a template excel file that we use in the office, this is were I get lost!

The excel form opens up fine but I have been asked if I can automatically force the excel file to save to the correct place (I have a filing system that attached documents are saved to) once the user has finished with it.

I have absolutely no idea if this is possible as I have never tried anything like this before!

Any help, even if it is just a confirmation that this is beyond the capability of access, is most appreciated!!

Cheers

Phil
It is definitely possible and quite easy to do. Plz provide me the code of the button used to open Excel template.
Jun 13 '07 #2

P: 7
It is definitely possible and quite easy to do. Plz provide me the code of the button used to open Excel template.
Cheers....I was hoping that it was possible but wasnt holding my breath!

Code below opens the template file.

Private Sub Template_Click()

template_filepath = "\\UK\Main\Projects\templates\Blank_Comments.x ls"
FollowHyperlink (template_filepath)

End Sub
Jun 13 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Cheers....I was hoping that it was possible but wasnt holding my breath!

Code below opens the template file.

Private Sub Template_Click()

template_filepath = "\\UK\Main\Projects\templates\Blank_Comments.x ls"
FollowHyperlink (template_filepath)

End Sub
Ok.

I'm not sure whether you can do it opening XL file via FollowHyperlink.

Here is another solution. It works fine.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Const FolderToSaveTo = "C:\Temp\"
  4. Const TemplatePath = "C:\template.xls"
  5.  
  6. Private Sub Template_Click()
  7.  
  8.     Dim appExcel As Excel.Application
  9.     Dim wkbWorkBook As Excel.Workbook
  10.  
  11.     On Error Resume Next
  12.  
  13.     Set appExcel = CreateObject("Excel.Application")
  14.  
  15.     With appExcel
  16.         .UserControl = True
  17.         Set wkbWorkBook = .Workbooks.Open(TemplatePath)
  18.         If Err <> 0 Then
  19.             MsgBox "Template file not found", vbOKOnly + vbCritical, "Error"
  20.             .Quit
  21.             GoTo ExitSub
  22.         End If
  23.         .Visible = True
  24.     End With
  25.  
  26.     wkbWorkBook.SaveAs FolderToSaveTo & UniqueName & ".xls"
  27.  
  28. ExitSub:
  29.     Set wkbWorkBook = Nothing
  30.     Set appExcel = Nothing
  31.  
  32. End Sub
  33.  
  34. Private Function UniqueName() As String
  35.     UniqueName = "CreatedOn - " & Format(Now, "mmm-dd-yyyy hh-mm-ss")
  36. End Function
  37.  
  38.  
Define your paths in rows 2,3

Good luck.
Jun 13 '07 #4

P: 7
What can I say other than thanks!!

Works a treat.

Cheers FishVal, you saved me from one hell of a headache!
Jun 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.