open, copy, and paste

[jacksonz]

I am almost done with a fairly large macro. I have it setup to do everything I want with the data I pull in. One problem, I don't know how to pull it in. I have a browse button that uses the getfilenameopen command and it dumps a path in a cell.

Let's say the path is in cell A1 on sheet1. I want sheet2 to look just like the file that's path is in sheet1!A1.

Also, for the getfilenameopen, how can I make it default to already have a particular drive selected. I have several, and I want P: to be the first drive I see instead of going through my computer....

Any help would be appreciated.

[FSt1]

hi,
here is some code i use. not sure if it will help you but maybe it will give
you ideas

Dim mybook As Workbook 'an excel workbook- used at line 34
Dim FNames As String 'workbook(or file) name - used at lines 20,
21, 33
Dim MyPath As String 'file path - use at lines 15,16,18,19,35
Dim SaveDriveDir As String 'current directory - used at lines
14,23,24,73,74
Dim cnt As Long 'counter to count Workbooks - used at lines
29,69,72
Dim cnt2 As Long
Dim cnt3 As Long
Dim c As String 'string to find in workbook - used at lines
31,32,36
Dim rng As Range 'range to search - used at lines
36,43,44,45,49,56,57,61,62,65
Dim Infocell As Range 'cell offset from rng. wanted info of rng
Dim sAddr As String 'range address of first find - used at lines
44,51,57,66

SaveDriveDir = CurDir 'Mark current directory
prior to search
'MyPath = InputBox("Enter a File Path") 'Get file path from inputbox
MyPath = "h:\excel\MRPLocQtyByDate\" 'Or state file path in code
'MyPath = Range("B3").Value 'Or get file path from cell
address
Range("B3").Value = MyPath 'display path in file(cell)
ChDrive MyPath 'change to path drive
ChDir MyPath 'change to path directory
FNames = Dir("*.xls") 'state type of file to search
If Len(FNames) = 0 Then 'if not xls then abort
MsgBox "No files in the Directory" 'message to user
ChDrive SaveDriveDir 'go back to previous drive
ChDir SaveDriveDir 'to back to previous directory
Exit Sub 'exit the code
End If


"jacksonz" wrote:

>
> I am almost done with a fairly large macro. I have it setup to do
> everything I want with the data I pull in. One problem, I don't know
> how to pull it in. I have a browse button that uses the
> getfilenameopen command and it dumps a path in a cell.
>
> Let's say the path is in cell A1 on sheet1. I want sheet2 to look just
> like the file that's path is in sheet1!A1.
>
> Also, for the getfilenameopen, how can I make it default to already
> have a particular drive selected. I have several, and I want P: to be
> the first drive I see instead of going through my computer....
>
> Any help would be appreciated.
>
>
> --
> jacksonz
> ------------------------------------------------------------------------
> jacksonz's Profile: http://www.excelforum.com/member.php...o&userid=13728
> View this thread: http://www.excelforum.com/showthread...hreadid=389040
>
>