Code Not Working On New Computer 1

[shelby55]

Hello

I have an Excel workbook that always worked before but giving me an error message now: "run time error 1004 - general odbc error".

The code is (provided by Skip):

Private Sub Worksheet_Activate()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

    With ActiveSheet
        With .QueryTables(1)
            .Connection = sConn
            Debug.Print .CommandText
            .Refresh False
        End With
        .Calculate
    End With
End Sub

The code is halted at .Refresh False.

This workbook uses MS Query from data on another page to summarize data for this page. I recently upgraded to another computer.

Thanks for any assistance.

 

[SkipVought]

in an empty cell, ENTER a [highlight]1[/highlight]

COPY the cell containing [highlight]1[/highlight]

SELECT the cells the contain the NUMBERS that you cannot change to dates
[highlight]
Right-Click > Paste Special > MULTIPLY[/highlight]

Now change the formats as desired.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

I did as you suggested and it copies as a date so it's a date..but why doesn't it calculate in the formula as a date?

 

[SkipVought]

What COPIES???

Do the NUMBERS format as DATES now?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

I wasn't understanding what you asked me to do so that is why I inserted another column, copied the number 1 down and in another column, multiplied the serial date by the 1 and it showed up as a date.

After your current query, I copied the 1, selected the serial dates, selected paste special, multiply and the column is now registering as "general" format.

So what does that mean?

 

[SkipVought]

but why doesn't it calculate in the formula as a date?

Your formula uses the underlying NUMBERS that may or may not be DISPLAYED as dates -- it makes no difference HOW they are displayed!

You must be absolutely certain that the values that appear to be NUMBERS or DATES are really NUMBERS and not just STRINGS OF DIGITS. What MULTIPLY by 1 does, is to coerce any STRINGS OF DIGITS to NUMBERS.

So you must do this in COLUMNS A & B and in ROW 1.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}