Frustrated with excel vba not working

[jvoss]

Frustrated at below code and any code. i have tried over 1000 ways to do one simple thing. to move data from 1 source workbook to thisworkbook.worksheet("summary") workbook. the below code is last ditch effort to get this to work ( over 50 hours trying to get the ( to work.)

now the code very simple
what is wrong?????????????????????????????????????????????????????????????????????????????????????????

Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Dim FileToOpen As Variant

 'Application.ScreenUpdating = False
    
    Dim src As Workbook
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
   
                                 
                                 FileToOpen = Application.GetOpenFilename _
                                 (Title:="Browse for your File & Import Range", _
                                 FileFilter:="Excel Files (*.xls*),*xls*")
    'If FileToOpen <> False Then '<------------------ disable when enabled it verifs a nill select is not passed.
     "end if
    
 Set src = Workbooks.Open(FileToOpen)            ' Set src = Workbooks.Open("C:\testbook.xlsm", True, True)
    
    ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).row).Rows.Count
  MsgBox iTotalRows '<------------- verify count of i matches with original files.
    ' COPY DATA FROM SOURCE  TO THE DESTINATION WORKBOOK.
    Dim iCnt As Integer         ' COUNTER.
    For iCnt = 1 To iTotalRows
     Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Sheet1").Range("B" & iCnt).Formula
  MsgBox src.Worksheets("Sheet1").Range("B" & iCnt).Formula '<-------------------------verify what is to be pasted in destination file (thisworkbook.)
    Next iCnt
    ' CLOSE THE SOURCE FILE.
    src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[jvoss]

source again

 

[MARK858]

Sub ReadDataFromCloseFile()
   
    Dim FileToOpen As String, src As Workbook, abook As Workbook

    Application.ScreenUpdating = False
   
    Set abook = ThisWorkbook
   
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    FileToOpen = Application.GetOpenFilename _
                 (Title:="Browse for your File & Import Range", _
                  FileFilter:="Excel Files (*.xls*),*xls*")

    If FileToOpen = False Then Exit Sub
   
   
    Set src = Workbooks.Open(FileToOpen)
   
    With src.Worksheets(CStr(src.VBProject.VBComponents("dataset1").Properties(7)))
        With .Range("A2:C" & .Range("A:C").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
            abook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    End With
   
    ' CLOSE THE SOURCE FILE.

    src.Close False                              ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
   
    Application.ScreenUpdating = True
End Sub

 

[jvoss]

Run-Time error '13':
Type Mismatch

on
FileToOpen=False Then Exit Sub.

 

[MARK858]

Change False to "False"

 

[jvoss]

Changed changed to variant now have the next issue
runtime error 1004
method 'VBPROJECT' of Object'_workbook'failed

if i remember correctly this has to be changed by the user in their excel so this can be trusted.
someting i would like to avoid as i will not be there to adjust their excel to work right.

 

[MARK858]

Don't change it to Variant when it is a string returned change False to "False".
Not tested but try the code below and report back

Sub ReadDataFromCloseFile()
   
    Dim FileToOpen As String, src As Workbook, abook As Workbook
    Dim sht As Worksheet, sheetCodeName As Worksheet
    Dim srcCName As String, shtChk As Boolean


    Application.ScreenUpdating = False
   
    Set abook = ThisWorkbook
   
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    FileToOpen = Application.GetOpenFilename _
                 (Title:="Browse for your File & Import Range", _
                  FileFilter:="Excel Files (*.xls*),*xls*")

    If FileToOpen = "False" Then Exit Sub
 
    Set src = Workbooks.Open(FileToOpen)
   
    srcCName = "Lines"

    For Each sht In src.Worksheets
        If sht.CodeName = srcCName Then
            Set sheetCodeName = sht
            shtChk = True
            Exit For
        End If
    Next sht

    If shtChk = True Then

        With sheetCodeName
            With .Range("A2:AC" & .Range("A:AC").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
                abook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
        End With
   
    End If
   
    ' CLOSE THE SOURCE FILE.

    src.Close False                              ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
   
    Application.ScreenUpdating = True
End Sub

 

[jvoss]

it look to be bypassing.
Set sheetCodeName = sht
shtChk = True
in the watch window the set sheetcodename is not setting and the shtChk is passing false.

 

[MARK858]

Sorry my fault I didn't change "Lines" to "dataset1"

Sub ReadDataFromCloseFile()
  
    Dim FileToOpen As String, src As Workbook, abook As Workbook
    Dim sht As Worksheet, sheetCodeName As Worksheet
    Dim srcCName As String, shtChk As Boolean


    Application.ScreenUpdating = False
  
    Set abook = ThisWorkbook
  
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    FileToOpen = Application.GetOpenFilename _
                 (Title:="Browse for your File & Import Range", _
                  FileFilter:="Excel Files (*.xls*),*xls*")

    If FileToOpen = "False" Then Exit Sub
 
    Set src = Workbooks.Open(FileToOpen)
  
    srcCName = "dataset1"

    For Each sht In src.Worksheets
        If sht.CodeName = srcCName Then
            Set sheetCodeName = sht
            shtChk = True
            Exit For
        End If
    Next sht

    If shtChk = True Then

        With sheetCodeName
            With .Range("A2:AC" & .Range("A:AC").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
                abook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
        End With
  
    End If
  
    ' CLOSE THE SOURCE FILE.

    src.Close False                              ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
  
    Application.ScreenUpdating = True
End Sub

 

[jvoss]

same results.

also just dug deeper on the source file the beep.beep.beep is a beep.beep.beep table. ie i think the term is object table. on the menu bar design table is orange

dose this change anything sorry just now look closer at source file.

 

[MARK858]

In this case no as I have just tested it with a table in the source sheet and it is copying the data as expected to the destination sheet.