Ignore show UserForm while read data

[amir0914]

Hi all,
I'm trying to read data from an excel file which the excel's visible is false and UserForm only is shown after run it.
I'm running this code in current excel file :

Application.ScreenUpdating = False
Dim appexcel As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set appexcel = GetObject(, "Excel.application")
Set wb = appexcel.Workbooks.Open("C:\Users\Windows\Desktop\maindata.xlsm")
Set ws = wb.Worksheets(1)
appexcel.Visible = False
...
...

But after run this code, user form is shown, is there a way to pull data from target xlsm without display UserForm or ignore it? (only reading data without seeing any userform)
Thanks in advanced.

 

[diddi]

are you trying to read data from a closed excel workbook?

 

[Jaafar Tribak]

Are you running that code from excel or from another application ?

 

[diddi]

i wondered that. would not this code instantiate a second copy of excel.exe?

 

[amir0914]

are you trying to read data from a closed excel workbook?

Yes, I want to read data from a closed workbbook (Application.Visible = False,frmMain.Show), And I'm running this code in another excel file : (The two files are Excel)

Dim appexcel As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set appexcel = GetObject(, "Excel.application")
Set wb = appexcel.Workbooks.Open("C:\Users\Windows\Desktop\maindata.xlsm")
Set ws = wb.Worksheets(1)

...
...

In another words I have two files :
1- An excel file of main data (Excel visible is false and only user form is shown after running)
2- An excel file of a macro to pull data from the first file

This first file is for users, users enter data in it by UserForm and I want to populate data by a macro into the second excel file on the weekend , now my problem is that frmMain in Excel 1 is shown after running codes in Excel 2,
Is there a way to read data from Excel 1 without running or displaying UserForm (frmMain ) ?

 

[diddi]

i have this in my snippits library. it is not my work but unfortunately i cannot give cred because i do not remember where it is from. it gets data from an excel workbook without opening it and is very fast.

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long

    ' Create the connection string.
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
    End If

    If SourceSheet = "" Then
        ' workbook level name
        szSQL = "SELECT * FROM " & SourceRange$ & ";"
    Else
        ' worksheet level name or range
        szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
    End If

    On Error GoTo SomethingWrong

    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1

    ' Check to make sure we received data and copy the data
    If Not rsData.EOF Then

        If Header = False Then
            TargetRange.Cells(1, 1).CopyFromRecordset rsData
        Else
            'Add the header cell in each column if the last argument is True
            If UseHeaderRow Then
                For lCount = 0 To rsData.Fields.Count - 1
                    TargetRange.Cells(1, 1 + lCount).Value = _
                    rsData.Fields(lCount).Name
                Next lCount
                TargetRange.Cells(2, 1).CopyFromRecordset rsData
            Else
                TargetRange.Cells(1, 1).CopyFromRecordset rsData
            End If
        End If

    Else
        MsgBox "No records returned from : " & SourceFile, vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing
    Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub

I place it in a module for standalone utilities in my project. if i need it i call it with this:

    GetData "D:\Documents\TimeSheet Data\NMF.xlsm", "D", "A4:J10000", Sheets("B").Range("KN1"), False, False

this would grab the file "NMF" get the data in sheet "D" in the range "A4:J10000" and insert it into my active workbook on sheet "B" starting at KN1. there is no header row

 

[amir0914]

Thank you so much, you did a great favor for me.
Is it possible to use "Where" clause in Select, Like :

szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] Where PaymentCode Not in ( select PaymentCode  from "Current Workbook");"

I want to carry it out to prevent reading duplicate data (reading the data that doesn't exist in current excel), do you think

 

[amir0914]

Do you think it's possible?

 

[diddi]

try it and see. SQL is not my thing

 

[amir0914]

I changed the function based on my own project by comparing two excels data :

Dim szConnect As String
Dim szSQL As String
Dim strCon As String
Dim strSQL As String
'''''''''''''''''''''''''''''''''''''''''''''''
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

strSQL = "SELECT * FROM [A1:J10000]"
    cn.Open strCon
    rs.Open strSQL, cn, 0, 1, 1
''''''''''''''''''''''''''''''''''''''''''''''
Dim rsCon As Object
Dim rsData As Object
 
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
SourceFile = "C:\Users\Windows\Desktop\test.xlsm"
SourceRange$ = "A1:J10000"
SourceSheet$ = "Data"

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=yes"";"

szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] WHERE [Name] Not in (select [Name] from rs) ;"
 
    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1

But I got the error massage after running in attached image
Can someone help me with the my project?