Connectionstring vba with blank space in dataname get broken

Question

My Code is following. I'll try to copy a range of data from a closed sheet with connectionstrings. The Code is okay if the dataname hasn't a empty string. e.g. test.xlsx is okay but test further.xlsx get broken.

    'using sql
Sub ImportThisFile(FilePath As String, SourceSheet As String, Destination As Range)
    Set Conn = New ADODB.Connection
    'xls
    'Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    '    FilePath & ";Extended Properties=Excel 8.0;"
    'xlsx
    Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
        FilePath & ";Extended Properties=Excel 12.0 Xml;"

    Sql = "SELECT * FROM [" & SourceSheet & "$]  WHERE [fieldname] <> " & [""""""]

    Set RcdSet = New ADODB.Recordset
    RcdSet.Open Sql, Conn, adOpenForwardOnly

    Destination.CopyFromRecordset RcdSet

    RcdSet.Close
    Set RcdSet = Nothing

    Conn.Close
    Set Conn = Nothing
End Sub

Sub StartDoingStuff()

    Dim Zeit As Long, Anzahl As Long

        Anzahl = 1
        Zeit = Timer
        Dim testvar As String, testvar2 As String, testvar3 As String
        testvar = "C:\Users\Admin\Desktop\Folder\"
        testvar2 = "testdata with emptystrings.xlsx.xlsx"
        testvar2 = "test.xlsx"

        testvar3 = "Tabelle1"

            ImportThisFile testvar & testvar2, "Timesheet", Range(testvar3 & "!A2")

        Debug.Print "Zeitbedarf"; Round(Timer - Zeit, 3)
End Sub

And Second Question. If I want to copy a range, how i must write the code? I need to determine the last cell in a column. How is that possible ?

@DavidZemens thx for input. But the Problem isn't the sql query, The Problem is the dataname (testvar2) — Paddymaster, Jun 25 '19 at 14:07
the second testvar2 = "test.xlsx" is commented with ' the first testvar hasn't two .xlsx. its a writing mistake — Paddymaster, Jun 25 '19 at 14:08
What, exactly, is the problem? Do you get error? What error, and what line raises the error? — David Zemens, Jun 25 '19 at 14:33
error msg: The Microsoft Access database engine could not find the object 'Timesheet$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Timesheet$' is not al ocal object, check your network connection or contact the server administrator. the problem is the name of testvar2. if testvar = test.xlsx then is everything ok. But if testvar = test with emptystrings.xlsx then i get the error — Paddymaster, Jun 25 '19 at 14:41
I don't think your assumption is correct. I'm not able to reproduce the error with a filename containing space characters. The error you describe will occur whenever you reference a file name that doesn't exist. Try it with `testVar2 = "foobar.xlsx"` (assuming no such file exists) and you'll get the same error. — David Zemens, Jun 25 '19 at 15:00
@DavidZemens last Questions. My Columns name are in line two and i dont know how many columns and rows i have. How can i write the code to detect the last columns and rows and copy that. current i copy the complete sheet — Paddymaster, Jun 25 '19 at 15:43
seems like a separate question TBH. I'm not sure and I don't have the time right now to try and figure that out. If it can be done with ADO. You might have to open the workbook and then you can do [these](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) to get last column/row. — David Zemens, Jun 25 '19 at 15:56

score 0 · Answer 1 · answered Jun 26 '19 at 07:40

0

the code i posted is correct. It was a mistake in the workbook (tablename)

answered Jun 26 '19 at 07:40

Paddymaster

70
1
10

Connectionstring vba with blank space in dataname get broken

1 Answers1