activesheet.range problem 1

[johnhugh]

Hi,

I'm trying to open an Excel sheet with VBA from within our fiance system.
My macro was working on my testmachine but now on a new machine I get a type mismatch error (error 13) on that line
For Each Cell In workbook.ActiveSheet.Range("A32:A202")

I think the way I create the Excel object is wrong?
Any help much appreciated.

Dim workbooks As Excel.workbooks
Set workbook = CreateObject("Excel.Sheet")

    workbook.Application.DisplayStatusBar = True
    workbook.Application.Visible = True
    workbook.Application.StatusBar = "Retrieving Currency Rates"
    
'   Perform the actual internet query to retrieve the uptodate exchange rates.

    With workbook.ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://www.xe.com/dfs/datafeed2.cgi",[/URL] Destination:=workbook.ActiveSheet.Range("A10"))      
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .HasAutoFormat = True
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SavePassword = False
        .SaveData = True
    End With
    
        
    curTables(0) = "USD"
    
    For Each curTable In curTables
    
        CurTableFields("HOMECUR").Value = curTable
        CurTableFields("RATETYPE").Value = rateType
        CSTables.Read
    
        CurRateFields("HOMECUR").Value = curTable
        CurRateFields("RATETYPE").Value = rateType
    
>>>Error<<<  For Each Cell In workbook.ActiveSheet.Range("A32:A202")
            CurCodeFields("CURID").Value = Trim(Cell.Value)
        ....

 

[SkipVought]

Hi,

both workbook & workbooks are reserve words.

Do not use reserve words as variables!

Skip,

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

 

[SkipVought]

What application does your program reside in?

Skip,

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

 

[johnhugh]

The financial system is called Accpac which can run macros in VBA.

 

[SkipVought]

here's an example of some of your code...

'declare an Excel Application object variable
    Dim xl As Excel.Application
'create an Excel Application object variable
    Set xl = CreateObject("Excel.Application")
'use an Excel Application object variable
    xl.DisplayStatusBar = True
    xl.Visible = True
    xl.StatusBar = "Retrieving Currency Rates"
'add a workbook
    With xl.workbooks.Add
'reference the first sheet in the added workbook
        With .Sheets(1)
'add a QT in the first sheet of the added workbook
            With .QueryTables.Add( _
                Connection:="URL;[URL unfurl="true"]http://www.xe.com/dfs/datafeed2.cgi",[/URL] _
                Destination:=.Range("A10"))
            
                .FieldNames = False
                .RefreshStyle = xlInsertDeleteCells
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .RefreshOnFileOpen = False
                .HasAutoFormat = True
                .BackgroundQuery = True
                .TablesOnlyFromHTML = True
                .Refresh BackgroundQuery:=False
                .SavePassword = False
                .SaveData = True
            End With
            
        End With
    End With

Skip,

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

 

[johnhugh]

Thank you. I got it working now.