Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error when getting data from another workbook
Hi,
The below macro gives me a type mismatch error, when i try and run it. It's run from Workbook1, which is named as variable ControlWorkbook. It open's up another workbook, variabled cc. The workbook name and sheet have the same name, hence worbooks and worksheets both relate to variable cc. Can someone please help. Thanks. ---- Public cc As Range ------------------------------------- Sub CreateReport() Dim r As Range Application.ScreenUpdating = False With Sheets("RecsC") Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)) For Each cc In r Workbooks.Open Filename:=strSavePath & strMonth & "\" & cc, _ UpdateLinks:=False, ReadOnly:=True Call GrabInvoiceData Next cc End With Application.ScreenUpdating = True End Sub ------------------------------------- Sub GrabInvoiceData() Dim a As Range Workbooks(ControlWorkbook).Activate With Sheets("Report") Set a = .Range("A" & Rows.Count).End(xlUp).Offset(0, 0) a = Workbooks(cc).Worksheets(cc).Range("B2").Value End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error when getting data from another workbook
1. You did Dim a As Range
Then you assing a cell.Value to it. This is type mismatch. Once dimmed as range you need to assing a Range Object to it not a value. e.g. a = Range("B2") 2. You did cc As Range. Topen the workbook the code needs a String in the name part not a Range Object. So in the Workbooks.Open line it should be & cc.Value, _ not only cc. Same way instead of WorkBooks(cc) it should be Workbooks(cc.Value), Worksheets(cc.Value). You defined cc as Public but please note that in the For each cc loop, when it comes out of the loop cc will refer only to the last cell in r, not all cells in r. Sharad "Tony Zappal" wrote in message ... Hi, The below macro gives me a type mismatch error, when i try and run it. It's run from Workbook1, which is named as variable ControlWorkbook. It open's up another workbook, variabled cc. The workbook name and sheet have the same name, hence worbooks and worksheets both relate to variable cc. Can someone please help. Thanks. ---- Public cc As Range ------------------------------------- Sub CreateReport() Dim r As Range Application.ScreenUpdating = False With Sheets("RecsC") Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)) For Each cc In r Workbooks.Open Filename:=strSavePath & strMonth & "\" & cc, _ UpdateLinks:=False, ReadOnly:=True Call GrabInvoiceData Next cc End With Application.ScreenUpdating = True End Sub ------------------------------------- Sub GrabInvoiceData() Dim a As Range Workbooks(ControlWorkbook).Activate With Sheets("Report") Set a = .Range("A" & Rows.Count).End(xlUp).Offset(0, 0) a = Workbooks(cc).Worksheets(cc).Range("B2").Value End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error when getting data from another workbook
Thanks Sharad,
I'll make the necessary changes. Thanks. Tony. "Sharad Naik" wrote: 1. You did Dim a As Range Then you assing a cell.Value to it. This is type mismatch. Once dimmed as range you need to assing a Range Object to it not a value. e.g. a = Range("B2") 2. You did cc As Range. Topen the workbook the code needs a String in the name part not a Range Object. So in the Workbooks.Open line it should be & cc.Value, _ not only cc. Same way instead of WorkBooks(cc) it should be Workbooks(cc.Value), Worksheets(cc.Value). You defined cc as Public but please note that in the For each cc loop, when it comes out of the loop cc will refer only to the last cell in r, not all cells in r. Sharad "Tony Zappal" wrote in message ... Hi, The below macro gives me a type mismatch error, when i try and run it. It's run from Workbook1, which is named as variable ControlWorkbook. It open's up another workbook, variabled cc. The workbook name and sheet have the same name, hence worbooks and worksheets both relate to variable cc. Can someone please help. Thanks. ---- Public cc As Range ------------------------------------- Sub CreateReport() Dim r As Range Application.ScreenUpdating = False With Sheets("RecsC") Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)) For Each cc In r Workbooks.Open Filename:=strSavePath & strMonth & "\" & cc, _ UpdateLinks:=False, ReadOnly:=True Call GrabInvoiceData Next cc End With Application.ScreenUpdating = True End Sub ------------------------------------- Sub GrabInvoiceData() Dim a As Range Workbooks(ControlWorkbook).Activate With Sheets("Report") Set a = .Range("A" & Rows.Count).End(xlUp).Offset(0, 0) a = Workbooks(cc).Worksheets(cc).Range("B2").Value End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Type Mismatch Error | Excel Discussion (Misc queries) | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Help with Error: Type Mismatch | Excel Programming | |||
Copying data to another worksheet gives "Type Mismatch" error | Excel Programming |