Open File Dialog and store opened file as variable

[KeriM]

I'm trying to open a file form the file dialog box and then store th
opened file as a variable so that I can reference it in the rest of m
code. Here is what I've got so far:


Code
-------------------

Sub selectiveCopy()
Dim bottom As Range, headerRow As Range, cell As Range
Dim targetCell As Range, targetSheet As Worksheet
Dim lastcell As Integer

'Here is where I want to select the file and store it as "sFileName". This works fine, but doesn't open the actual file, just gets the path.

sFileName = Application.GetOpenFilename

'In order to open the file, I have to run this:

Application.FileDialog(msoFileDialogOpen)
.Show
.Execute

' Any way I can just use one?

Set headerRow = ActiveSheet.Range("1:1")

'Here is where I want to start calling the file variable "sFileName" I get an "object required" error.
Set targetSheet = sFileName
sFileName.Activate

targetSheet.Cells(1, 1).Select
lastcell = targetSheet.Range("A1").End(xlDown).Row
Set targetCell = targetSheet.Cells(1, 1)
For Each cell In headerRow
Select Case cell.Value
Case "value1 to copy", "value2 to copy", "value3 to copy"
Set bottom = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, _
cell.Column)
If bottom.Value <> "" Then
Range(cell.Address & ":" & bottom.Address).Copy
Else
Range(cell.Address & ":" & Cells(bottom.End(xlUp).Row, _
cell.Column).Address).Copy
End If
targetSheet.Paste Destination:=targetCell
Set targetCell = targetCell.Offset(0, 1)
targetSheet.Paste Destination:=targetCell
End Select
Next
End Sub


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


I also have another sub after this one to select that opened fil
(sFileName) after the loop is done, but maybe we can activate it in thi
sub somewhere after the loop? I'm still new at VBA, so I'm not sure wha
can be done. Any help is greatly appreciated

 

[Auric__]

I'm trying to open a file form the file dialog box and then store the
opened file as a variable so that I can reference it in the rest of my
code. Here is what I've got so far:


Code:
--------------------

Sub selectiveCopy()
Dim bottom As Range, headerRow As Range, cell As Range
Dim targetCell As Range, targetSheet As Worksheet
Dim lastcell As Integer

This is optional, but here I would add this:
Dim sFileName As Workbook

'Here is where I want to select the file and store it as "sFileName".
This works fine, but doesn't open the actual file, just gets the path.

sFileName = Application.GetOpenFilename

From the helpfile:

Application.GetOpenFilename Method

Displays the standard Open dialog box and gets a file name from the user
without actually opening any files.

You use GetOpenFilename to get the file to open -- which is returned as a
string containing the path to the file -- then you actually open it like
this:
tmp = Application.GetOpenFilename
If False = tmp Then Exit Sub
Set sFileName = Workbooks.Open(tmp)

'In order to open the file, I have to run this:

Application.FileDialog(msoFileDialogOpen)
.Show
.Execute

' Any way I can just use one?

The above block isn't needed.

Set headerRow = ActiveSheet.Range("1:1")

'Here is where I want to start calling the file variable "sFileName" I
get an "object required" error.
Set targetSheet = sFileName

Can't set a worksheet to a workbook. It should be more like this:
Set targetSheet = sFileName.Sheets(1)

sFileName.Activate

targetSheet.Cells(1, 1).Select
lastcell = targetSheet.Range("A1").End(xlDown).Row
Set targetCell = targetSheet.Cells(1, 1)
For Each cell In headerRow
Select Case cell.Value
Case "value1 to copy", "value2 to copy", "value3 to copy"
Set bottom = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, _
cell.Column)
If bottom.Value <> "" Then
Range(cell.Address & ":" & bottom.Address).Copy
Else
Range(cell.Address & ":" & Cells(bottom.End(xlUp).Row, _
cell.Column).Address).Copy
End If
targetSheet.Paste Destination:=targetCell
Set targetCell = targetCell.Offset(0, 1)
targetSheet.Paste Destination:=targetCell
End Select
Next
End Sub


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


I also have another sub after this one to select that opened file
(sFileName) after the loop is done, but maybe we can activate it in this
sub somewhere after the loop? I'm still new at VBA, so I'm not sure what
can be done. Any help is greatly appreciated!

If by "select" you mean "make the active workbook", then put this right
before 'End Sub':
targetSheet.Activate

....or perhaps you want this instead:
sFileName.Activate

....but you already have that a few lines above the Select Case. Shrug.