asking for a file

M

Marie Lavoie

Hi, I wondered if there would be a command so that it asks the user to chose
what file to open.

I've made a program that updates the info in the second sheet when you press
the button in the first sheet. But I'd need to keep the result of the
previous month in the second sheet before updating. So right now, the user
has to copy the information. Is there a way that the programs open a "open"
box where he selects the excel file and that it copies it in the 2nd sheet?
(And if you have even better ideas, you may submit it ;-) )

Thank you.

Marie
 
K

Kris

Marie,

Dim Myfilename as String

Myfilename = Application.GetOpenFilename
Workbooks.Open FileName:=MyFilename

Regards,
Kris
 
M

Michael Malinsky

I found a file by Jim Rech called BrowseForFolder.zip. This gives you all
the code to help create a pop-up dialog that allows the user to browse to a
specific file. I've only had the opportunity to incorporate it into a
couple of modules, but it seems to work find.

The file can be found at http://www.bmsltd.ie/MVP/MVPPage.asp

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
T

Tom Ogilvy

I believe it allows you to browse to a specific folder/directory, not a
specific file.
 
M

Marie Lavoie

That helped me, ty. And you answered to my question, as I wanted to open a
file.
But may the code add the sheets to the ones I have right now? Or do I need
to make the macro open the file, copy selected sheet, paste in first work
sheet??

Marie
 
M

Marie Lavoie

Could someone tell me whats wrong with the code below?

Sub Parcourir()
FichierAOuvrir = Application.GetOpenFilename
If FichierAOuvrir <> Faux Then
TextBox1.Text = FichierAOuvrir
End If
End Sub


I want to have a textbox where the file name with it's whole path appears.
I wrote this in a blank sheet as a test. Btq, TextBox1 exists and
FichierAOuvrir is declared as string. But is shows mistakes.
At first my 3rd line was "If FichierAOuvrir <> False Then" ans it kept
bugging, so I put "faux" ans it works... Frustrating to program with a
french VB... what a weird Idea to put french words like this... anyway.
Now, I know that FichierAOuvrir = "Path is written here"
But it doesn't like the "TextBo1.Text = FichierAOuvrir". Could you tell me
whats wrong?

Thnak you for your help.

Marie
 
M

Michael Malinsky

Tom,

I double checked before I posted my response and there is an option that
allows you to browse to a specific file.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
M

Marie Lavoie

"userform1.textbox1.text" .... doh.
Ok.
But still, my question about copying a worksheet hasn't been answered.

Marie
 
T

Tom Ogilvy

I stand corrected.

--
Regards,
Tom Ogilvy

Michael Malinsky said:
Tom,

I double checked before I posted my response and there is an option that
allows you to browse to a specific file.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
Click to expand...
 
T

Tom Ogilvy

Sub Parcourir()set sh = Activesheet
set wkbk = workbooks.open(Filename:=FichierAOuvrir)
sh.UsedRange.copy
wkbk.workbooks(2).Cells(rows.count,1).End(xlup)(2).Paste xlValues
wkbk.Close Savechanges:=True
 
K

Kris

Marie,

Sorry, forgot about your second question....

Anyway, I was unable to find a more elegant solution than
this for copying one worksheet to another (in Excel 97
anyway, which is what I am still using) since you can't
do a straight assignment of worksheets like:

worksheets("sheet2") = worksheets("sheet1")

or assignment of ranges like:

Worksheets("sheet2").Range("A1:B5") = _
Worksheets("sheet1").Range("A1:B5")

Therefore, a copy and paste method is needed.

----------------------------------------------------------

Dim lastrow As Integer
Dim lastcol As Integer

lastrow = Worksheets("sheet1").UsedRange.Row + _
Worksheets("sheet1").UsedRange.Rows.Count - 1
lastcol = Worksheets("sheet1").UsedRange.Column + _
Worksheets("sheet1").UsedRange.Columns.Count - 1

Worksheets("sheet1").Range("A1", _
Worksheets("sheet1").Cells(lastrow, lastcol)).Copy
Worksheets("sheet2").Range("A1").PasteSpecial

----------------------------------------------------------

I hope that this answers your question.

Regards,
Kris
 
M

Marie Lavoie

Sorry Tom, but it doesn't work.
Here is what I wrote.
I have a UserForm with a textbox and 2 buttons, one that runs parcourir and
the other that runs ouvrir.
Also, I'm not sure to understand every line, but It seems like you copy the
sheet in the workbook I open. (No?) Anyway, only to be sure, I want the
sheet I open to be copied in the workbook were I run the macro.
I put in comments what I thought I understood...

Thank you for your help, we're almost there.

Marie

----
Dim FichierAOuvrir As String

Sub Ouvrir()
Set sh = ActiveSheet 'So sh
would be the sheet were I run the macro?
Set wkbk = Workbooks.Open(Filename:=FichierAOuvrir) 'And wkbk opens the
indicated file?
sh.UsedRange.Copy ' That
would copy the data in my first sheet
wkbk.Workbooks(2).Cells(Rows.Count, 1).End(xlUp)(2).Paste xlValues Then
open a file and paste it. What is the code in the brackets?
wkbk.Close Savechanges:=False 'when it closes
the workbook, is it the previous sheet (in previous workbook) that is
active?
UserForm1.Hide

End Sub

Sub Parcourir()
FichierAOuvrir = Application.GetOpenFilename("Fichiers Excel (*.xls;*.xlt),
*.xls;*.xlt", , "Selectionner le rapport du mois dernier")
If FichierAOuvrir <> Faux Then
UserForm1.TextBox1.Text = FichierAOuvrir
End If
End Sub
 
Top