Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Open File Dialog and store opened file as variable
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 '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 Last edited by KeriM : July 23rd 12 at 05:17 PM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File Dialog and store opened file as variable
KeriM wrote:
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. -- Abandon your foolish quest. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't open a file I just opened the day before. | Excel Discussion (Misc queries) | |||
cannot open the excel file, the file is already opened | Excel Discussion (Misc queries) | |||
File Browse Function (Open dialog box for choosing file) | Excel Programming | |||
How set file open path to filepath of file opened with Explorer ? | Excel Programming | |||
open file dialog-select file-import worksheet | Excel Programming |