3 issues in excel

[adinic]

Hello. I am working with an excel worksheet and i need the following:

1. A function that allows me to display the date when the file was last modiffied.
2. A button that will open a save window at a predefinded path so that i can enter the file name and press save.
3 In this file i am working with links. I need another function with a button or something that will open a window to a predefined path so that i can select the file that i what the links to point and press ok or open.
I need the last two functions because they would save a lot of time for me. Any help would be very much apreciated. Thank you!!

[Kevin B]

In the workbook's module, put code that will post the date/time of the last
save in the before save event.

In the example below the date and time are posted to cells A1 & A2
respectively:
======================================================

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = Date
ActiveWorkbook.Sheets("Sheet1").Range("A2").Value = Time

End Sub

====================================================
--
Kevin Backmann


"adinic" wrote:

>
> Hello. I am working with an excel worksheet and i need the following:
>
> 1. A function that allows me to display the date when the file was last
> modiffied.
> 2. A button that will open a save window at a predefinded path so that
> i can enter the file name and press save.
> 3 In this file i am working with links. I need another function with a
> button or something that will open a window to a predefined path so
> that i can select the file that i what the links to point and press ok
> or open.
> I need the last two functions because they would save a lot of time for
> me. Any help would be very much apreciated. Thank you!!
>
>
> --
> adinic
> ------------------------------------------------------------------------
> adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
> View this thread: http://www.excelforum.com/showthread...hreadid=512215
>
>

[Kevin B]

Here's one that will save your file to a specified directory. You can place
this code in a general module and then assign it to a custom toolbar button,
or a button that you place on the worksheet.

======================================================
Sub SaveMeHere()

Dim strPath As String
Dim strFileName As String

On Error GoTo ExitSave

strPath = "Your Full Path Goes Here"
strFileName = InputBox("Enter the name you wish to save this file as.")

ActiveWorkbook.SaveAs strPath & strFileName

ExitSave:

If Err.Number > 0 Then
If Err.Number = 1004 Then Exit Sub
MsgBox "An error has occured trying to save your file."
Exit Sub
End If

End Sub
======================================================

--
Kevin Backmann


"adinic" wrote:

>
> Hello. I am working with an excel worksheet and i need the following:
>
> 1. A function that allows me to display the date when the file was last
> modiffied.
> 2. A button that will open a save window at a predefinded path so that
> i can enter the file name and press save.
> 3 In this file i am working with links. I need another function with a
> button or something that will open a window to a predefined path so
> that i can select the file that i what the links to point and press ok
> or open.
> I need the last two functions because they would save a lot of time for
> me. Any help would be very much apreciated. Thank you!!
>
>
> --
> adinic
> ------------------------------------------------------------------------
> adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
> View this thread: http://www.excelforum.com/showthread...hreadid=512215
>
>

[adinic]

Thank you very much for the code you wrote. The save function worked like a charm.. But the last saved date didn't. And i don't know why. I will keep trying.

Worked. You are genius!

[adinic]

Hello. Can someone please tell me how to make a function that will import into my current worksheet certain cells from another xls file. The thing is that i want to be able to select the folder and the file from which i import data, because the file from which i import changes.

[Chip Pearson]

The following code should get you started.


Dim FName As Variant
Dim WB As Workbook
Dim DestRng As Range
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName = False Then
Exit Sub ' user didn't select any file
End If
' open the source workbook
Set WB = Workbooks.Open(Filename:=FName)
' copy cell Sheet1!A1 from WB to ThisWorkbook
WB.Worksheets("Sheet1").Range("A1").Copy _
Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A1")
' close the sourse workbook
WB.Close savechanges:=False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"adinic" <[email protected]>
wrote in message
news:[email protected]...
>
> Hello. Can someone please tell me how to make a function that
> will
> import into my current worksheet certain cells from another xls
> file.
> The thing is that i want to be able to select the folder and
> the file
> from which i import data, because the file from which i import
> changes.
>
>
> --
> adinic
> ------------------------------------------------------------------------
> adinic's Profile:
> http://www.excelforum.com/member.php...o&userid=31529
> View this thread:
> http://www.excelforum.com/showthread...hreadid=512215
>

[adinic]

Than you very much for this code. I may need another thing. Can you please give me your e-mail or your yahoo id. If you want.. It would help me a lot! Thank you again!

[Chip Pearson]

Adnic,

Put the following code in a general code module, not the
ThisWorkbook module or a sheet module:


Sub AAA()
Dim FName As Variant
Dim WB As Workbook
Dim DestRng As Range
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName = False Then
Exit Sub ' user didn't select any file
End If
' open the source workbook
Set WB = Workbooks.Open(Filename:=FName)
' copy cell Sheet1!A1 from WB to ThisWorkbook
WB.Worksheets("Sheet1").Range("A1").Copy _
Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A1")
' close the sourse workbook
WB.Close savechanges:=False
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"adinic" <[email protected]>
wrote in message
news:[email protected]...
>
> Than you very much for this code. But you forgot to tell me
> where to put
> it and how to use the function. I would apreciate that very
> much! Thank
> you!
>
>
> --
> adinic
> ------------------------------------------------------------------------
> adinic's Profile:
> http://www.excelforum.com/member.php...o&userid=31529
> View this thread:
> http://www.excelforum.com/showthread...hreadid=512215
>

[adinic]

Thank you very much. It worked. I would need this function to open the window at a predifined path. How can i do that?? Thank you so much for all your help!

[adinic]

Thank you very much. It worked. I would need this function to open the window at a predifined path. How can i do that?? Thank you so much for all your help!

[Chip Pearson]

I don't understand your question.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"adinic" <[email protected]>
wrote in message
news:[email protected]...
>
> Thank you very much. It worked. I would need this function to
> open the
> window at a predifined path. How can i do that?? Thank you so
> much for
> all your help!
>
>
> --
> adinic
> ------------------------------------------------------------------------
> adinic's Profile:
> http://www.excelforum.com/member.php...o&userid=31529
> View this thread:
> http://www.excelforum.com/showthread...hreadid=512215
>

[adinic]

I am talking about the function that imports data from another xls file. I created a button and assigned that function to it. When i click this button a window opens. I need that window to open at a default path. I want to do this because otherwise i would have to search for the file from which i want to import the data. Is there anyway i could do that? I found a way but it doesn't solve the problem completely(Tools-options-general-default file location).

I have another question for you. I have a file to which i want to import from another file certain cells. I want a function that will allow me to select the row where i import the data. I atached a file for you to see what i mean(i changed the extension to .doc;please change it to xls). In that file i need cells g11,h11,k11,l11,n11,o11,u11 to be replaced with cells from another file. I need that for any row not only for row 11. This would make my work a lot easier. Thank you very much!

[Chip Pearson]

If you talking about the file open window, you can change the
default open directory using code like


Dim Fname As Variant
ChDrive "H:" '<<< CHANGE
ChDir "H:\Test2" '<<< CHANGE
Fname = Application.GetOpenFilename()
MsgBox Fname


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"adinic" <[email protected]>
wrote in message
news:[email protected]...
>
> I am talking about the function that imports data from another
> xls file.
> I created a button and assigned that function to it. When i
> click this
> button a window opens. I need that window to open at a default
> path. I
> want to do this because otherwise i would have to search for
> the file
> from which i want to import the data. Is there anyway i could
> do that?
> I found a way but it doesn't solve the problem
> completely(Tools-options-general-default file location).
>
> I have another question for you. I have a file to which i want
> to
> import from another file certain cells. I want a function that
> will
> allow me to select the row where i import the data. I atached a
> file
> for you to see what i mean(i changed the extension to
> .doc;please
> change it to xls). In that file i need cells
> g11,h11,k11,l11,n11,o11,u11 to be replaced with cells from
> another
> file. I need that for any row not only for row 11. This would
> make my
> work a lot easier. Thank you very much!
>
>
> +-------------------------------------------------------------------+
> |Filename: indicatori.doc
> |
> |Download: http://www.excelforum.com/attachment.php?postid=4370
> |
> +-------------------------------------------------------------------+
>
> --
> adinic
> ------------------------------------------------------------------------
> adinic's Profile:
> http://www.excelforum.com/member.php...o&userid=31529
> View this thread:
> http://www.excelforum.com/showthread...hreadid=512215
>

[adinic]

Thank you! It worked! But what about the other thing ? I searched the internet but wasn't able to find anything helpful.If you could help one last time i would be very grateful! Thank you!

[adinic]

Ok. In the file called indicatori i want cells bx, cx,gx, hx,kx,ox,ux(x is the row number) to be replaced with cells d8,d9,d12,d15,d96,l65,j51 from another file. The file from which i import will change all the time, but it will have the same layout, so the reference to the cells in that file will remain. So i would need a vba function to assign to a button. By pressing the button i would need an open window to open a predifined path, i would select the file from which i import the data and that is pretty much it! Thanks for all your help!

[adinic]

Nobody knows how to do this??