VBA open file to pull data from, if unable ask what file to pull from

[pookexvi]

I'm trying to have it pull data from a separate file got that working fine. when I try to get it to ask what file if it cant automatically find the fie is when I run in to problems. (i cant get it to say with 'else' in the code)

 If Range("'cure data'!$A$1").Value = "" Then Workbooks.Open "F:\TMA01\TMA01002.xls"
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False
    
    else
     With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If SelectedItems.Count > 0 Then
    Workbooks.Open .SelectedItems(1)
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False

 

[johnnyL]

Maybe this:

    If Range("'cure data'!$A$1").Value = "" Then
        Workbooks.Open "F:\TMA01\TMA01002.xls"
    Else
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
            .AllowMultiSelect = False
            .Show
'
            If SelectedItems.Count > 0 Then Workbooks.Open .SelectedItems(1)
        End With
    End If
'
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False

 

[pookexvi]

Maybe this:

    If Range("'cure data'!$A$1").Value = "" Then
        Workbooks.Open "F:\TMA01\TMA01002.xls"
    Else
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
            .AllowMultiSelect = False
            .Show
'
            If SelectedItems.Count > 0 Then Workbooks.Open .SelectedItems(1)
        End With
    End If
'
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False

comes up with run time errror '1004'
couldnt find "F:\TMA01\TMA01002.xls"

 

[johnnyL]

Now I understand your question.

    Dim DefaultFileExists   As Boolean
    Dim DefaultFileName     As String
'
    DefaultFileName = "F:\TMA01\TMA01002.xls"
    On Error Resume Next
    DefaultFileExists = (Dir(DefaultFileName) <> "")
    On Error GoTo 0
'
    If Range("'cure data'!$A$1").Value = "" And DefaultFileExists Then
        Workbooks.Open DefaultFileName
    Else
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
            .AllowMultiSelect = False
            .Show
'
            If SelectedItems.Count > 0 Then Workbooks.Open .SelectedItems(1)
        End With
    End If
'
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False

 

[pookexvi]

Now I understand your question.

    Dim DefaultFileExists   As Boolean
    Dim DefaultFileName     As String
'
    DefaultFileName = "F:\TMA01\TMA01002.xls"
    On Error Resume Next
    DefaultFileExists = (Dir(DefaultFileName) <> "")
    On Error GoTo 0
'
    If Range("'cure data'!$A$1").Value = "" And DefaultFileExists Then
        Workbooks.Open DefaultFileName
    Else
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
            .AllowMultiSelect = False
            .Show
'
            If SelectedItems.Count > 0 Then Workbooks.Open .SelectedItems(1)
        End With
    End If
'
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False

THANK YOU. this is exactly what I was looking for.

 

[johnnyL]

Glad to help.

 

[pookexvi]

it worked a few times now I'm getting a new error. i hadn't changed the code at all.

run time error '424'

highlights 'wkbCrntWorkBook.Activate' near the botttom of the code you gave

 

[johnnyL]

That is your code, that you provided on post #1. I only added code at the top to check if the default file exists.

 

[pookexvi]

here is the entire code

Sub Ctest()

Sheets("Analysis").Range("B2:B2", "B6:B6").ClearContents
Sheets("Cure Data").Range("A1:J1900", "A1: L2000").ClearContents
Sheets("Analysis").Range("B8:B8", "B8:B8").ClearContents

Sheets("Cure Data").Select
Cells(1, 1).Select
Sheets("Analysis").Select

    Dim DefaultFileExists   As Boolean
    Dim DefaultFileName     As String
'
    DefaultFileName = "F:\TMA01\TMA01002.xls"
    On Error Resume Next
    DefaultFileExists = (Dir(DefaultFileName) <> "")
    On Error GoTo 0
'
    If Range("'cure data'!$A$1").Value = "" And DefaultFileExists Then
        Workbooks.Open DefaultFileName
    Else
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xls"
            .AllowMultiSelect = False
            .Show
'
            If SelectedItems.Count > 0 Then Workbooks.Open .SelectedItems(1)
        End With
    End If
'
    Set wkbSourceBook = ActiveWorkbook
    Set rngSourceRange = Range("A1:L2000")
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("'cure data'!$A$1")
    rngSourceRange.Copy rngDestination
    rngDestination.CurrentRegion.EntireColumn.AutoFit
    wkbSourceBook.Close False
 
End Sub

 

[johnnyL]

I don't see where you have told excel what 'wkbCrntWorkBook' is.