sql in vba - could not find a file

[radoslaw19]

Hi
I am tryign to get this work, yet i simply cannot find why this macro does not find a file

the error message is as follows: ms database engine could not find the object sheet1 - it is a runtime error

the files which i want to export values to, exist for sure - i created them myself

Could you please help me with this?

Sub output_into_files()


Dim CONNECT As New ADODB.Connection
Dim query1 As New ADODB.Recordset
Dim path As String
Dim name As String
Dim a As Integer
Dim sheetValue As String
Dim QueryString As String




path = "C:\RS_DESKTOP\vba\VBA COURSE - 8-9.12.14\DANE\for external output\"


name = Dir(path)


a = 1
sheetValue = "sheet" & a


Do Until name = ""


    CONNECT.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & path & name & ";"
    
    QueryString = "INSERT INTO sheet" & a & " VALUES ('test')"
    
Call query1.Open(QueryString, CONNECT)
CONNECT.Close
        
    name = Dir
    a = a + 1
Loop


End Sub

[SamT]

I don't know if it makes a difference, but sheetValue is a constant ("sheet1") and QueryString uses "sheet " & a

[radoslaw19]

I don't know if it makes a difference, but sheetValue is a constant ("sheet1") and QueryString uses "sheet " & a

hi
i need to keep it as variable due to the fact that i need to use it for many files - is there anything else that might help?

[SamT]

I really don't know enough about Queries to be definate about this.

Looking at the QueryString, I think that maybe:

In the Workbook's VBA Editor, the Project Explorer, (see View menu,) must be showing a "Sheet1" without the parenthesis.

It doesn't need to have a "(Sheet1)"

In the VBAProject Explorer, the Non-Parenthesized name is the sheet's CodeName, and the One with Parens is the Sheet's Name, often called it's Tab name.

Try this code and see if it changes things

a = 1

Do Until Name = ""

    sheetValue = "Sheet" & a
    Connect.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & Path & Name & ";"
    QueryString = "INSERT INTO " & sheetValue & " VALUES ('test')"
    
Call query1.Open(QueryString, Connect)
Connect.Close
        
    Name = Dir
    a = a + 1

Try

QueryString = "INSERT INTO Sheets(""" & sheetValue & """) VALUES ('test')"

The correct number of double quotes is on you

The resulting two Query strings I am thinking of are
1.) INSERT INTO Sheet1 VALUES ('test') [Uses Sheet1's CodeName]
and
2.) INSERT INTO Sheets("Sheet1") VALUES ('test') [Uses Sheets1's Tab Name.]

[radoslaw19]

Hi
thanks a lot, i will test this and try to understand how it works, appreciate you answer