I'm having a problem with the line:
Set wsOutsideFile = Application.Workbooks.Open("C:\myfile.xls").Worksh eets(1)

I've changed the C:\myfile.xls to the path and file name of the file I want
to open. It opens the file, but then immediately crashes with the error:

"Object Variable or With Block Variable Not set."

I've put in some MsgBox "Test" values to check where exactly the process
fails. It opens the file, but then crashes before it gets to the next line.
So It's got to be crashing there.

here's the line in my code:


Sub getGroups()
'This method will open the external .xls file and read in the DFR Groups.

Dim ofFile As Worksheet 'Create a variable to hold the worksheet.
Dim lastRow As Long 'Used to hold the value of last row on the sheet.
Dim i As Integer 'Used to incriment For Loop.
Dim x As Integer 'Used to incriment the row count.
Dim dfr As String 'Used to hold the dfr name. (Group Name)

MsgBox "Test 1"

ofFile = Application.Workbooks.Open("c:\item
grouping.xls").Worksheets(1) 'Open the workbook as ofFile.
MsgBox "Test 2"
lastRow = ofFile.Cells(Rows.Count, "B").End(xlUp).Row 'Get the last row
on the page.
MsgBox "Test 3"
x = 3 'Set the x value to 3.
MsgBox "Test 4"
dfr = "" 'Set the dfr string to empty.

MsgBox "Test 5"

'Scroll through the list of Groups, get the first group, then everytime
the group name
'changes after that, put in the new grouplist.
For i = 1 To lastRow 'Loop through to the end of the page, from bottom up.
'If "dfr" does not equal "ofFile.Cells(i,"B").Value" then do the
following, otherwise do nothing.
If dfr < ofFile.Cells(i, "B").Value Then
dfr = ofFile.Cells(i, "B").Value 'Set dfr to the name in the cell.
Cells(x, "G").Value = dfr 'Set the specified cell in the invList
workbook to the string dfr.
x = x + 1 'Incriment x.
End If
Next i 'Increment counter and reloop

End Sub 'End sub getGroups()


"Dave Ramage" wrote:

Something like this:

Sub Demo1()
Dim wsOutsideFile as Worksheet
Dim lLastRow as Long
Dim rngGroupingIDs as Range
Dim v as Variant
Dim strResult as String

'open file an set a pointer to the first worksheet
Set wsOutsideFile =
Application.Workbooks.Open("C:\myfile.xls").Worksh eets(1)

'''do something complicated
'find the last row in the list
lLastRow = wsOutsideFile.Range("A65536").End(xlUp).Row
'set range object to point to Grouping ID columns (assume column A and B,
'starting in cell A1)
Set rngGroupingIDs = wsOutsideFile.Range("A1").Resize(lLastRow,2)
'now do something with this range of data
'e.g. lookup value in column 1 and return coresponding value in column 2
v= Application.Match("MyLookupID", rngGroupingIDs.Columns(1),0)
If IsError(v) Then
Msgbox "Value not found!"
Else
strResult = rngGroupingIDs.Cells(v,2).Formula
MsgBox "Result found: " & strResult
End If

'''or maybe just return the value from a pecified row/column
MsgBox wsOutsideFile.Range("B3").Value
MsgBox wsOutsideFile.Cells(3,2).Value 'Cells(RowNum,ColumnNum)

'now tidy up
Set rngGroupingIDs = Nothing
wsOutsideFile.Parent.Close SaveChanges:=False
End Sub

..it all depends what you want to do with the data in the sheet, but this
should get you started.

Cheers,
Dave

"RSteph" wrote:

I've got a spreadsheet that has a list of items and the grouping that they
belong to. I also have a .csv file that I open with excel and turn into a
spreadsheet. I'm making a macro to do some formatting/calculating/etc. within
the excel sheet made from the .csv.

What I would like to do is read in the values from the other excel file. The
file that I've got now has a list of items and quantities. I want to read in
the outside file (2 column file), to read off and insert the grouping ID's,
and to do some comparrisons with.

How would I go about reading in the file, and pointing to a column and/or
row within the list?

You dropped "Set" from the line. Put it back.

RSteph wrote:

I'm having a problem with the line:
Set wsOutsideFile = Application.Workbooks.Open("C:\myfile.xls").Worksh eets(1)

I've changed the C:\myfile.xls to the path and file name of the file I want
to open. It opens the file, but then immediately crashes with the error:

"Object Variable or With Block Variable Not set."

I've put in some MsgBox "Test" values to check where exactly the process
fails. It opens the file, but then crashes before it gets to the next line.
So It's got to be crashing there.

here's the line in my code:

Sub getGroups()
'This method will open the external .xls file and read in the DFR Groups.

Dim ofFile As Worksheet 'Create a variable to hold the worksheet.
Dim lastRow As Long 'Used to hold the value of last row on the sheet.
Dim i As Integer 'Used to incriment For Loop.
Dim x As Integer 'Used to incriment the row count.
Dim dfr As String 'Used to hold the dfr name. (Group Name)

MsgBox "Test 1"

ofFile = Application.Workbooks.Open("c:\item
grouping.xls").Worksheets(1) 'Open the workbook as ofFile.
MsgBox "Test 2"
lastRow = ofFile.Cells(Rows.Count, "B").End(xlUp).Row 'Get the last row
on the page.
MsgBox "Test 3"
x = 3 'Set the x value to 3.
MsgBox "Test 4"
dfr = "" 'Set the dfr string to empty.

MsgBox "Test 5"

'Scroll through the list of Groups, get the first group, then everytime
the group name
'changes after that, put in the new grouplist.
For i = 1 To lastRow 'Loop through to the end of the page, from bottom up.
'If "dfr" does not equal "ofFile.Cells(i,"B").Value" then do the
following, otherwise do nothing.
If dfr < ofFile.Cells(i, "B").Value Then
dfr = ofFile.Cells(i, "B").Value 'Set dfr to the name in the cell.
Cells(x, "G").Value = dfr 'Set the specified cell in the invList
workbook to the string dfr.
x = x + 1 'Incriment x.
End If
Next i 'Increment counter and reloop

End Sub 'End sub getGroups()

"Dave Ramage" wrote:

Something like this:

Sub Demo1()
Dim wsOutsideFile as Worksheet
Dim lLastRow as Long
Dim rngGroupingIDs as Range
Dim v as Variant
Dim strResult as String

'open file an set a pointer to the first worksheet
Set wsOutsideFile =
Application.Workbooks.Open("C:\myfile.xls").Worksh eets(1)

'''do something complicated
'find the last row in the list
lLastRow = wsOutsideFile.Range("A65536").End(xlUp).Row
'set range object to point to Grouping ID columns (assume column A and B,
'starting in cell A1)
Set rngGroupingIDs = wsOutsideFile.Range("A1").Resize(lLastRow,2)
'now do something with this range of data
'e.g. lookup value in column 1 and return coresponding value in column 2
v= Application.Match("MyLookupID", rngGroupingIDs.Columns(1),0)
If IsError(v) Then
Msgbox "Value not found!"
Else
strResult = rngGroupingIDs.Cells(v,2).Formula
MsgBox "Result found: " & strResult
End If

'''or maybe just return the value from a pecified row/column
MsgBox wsOutsideFile.Range("B3").Value
MsgBox wsOutsideFile.Cells(3,2).Value 'Cells(RowNum,ColumnNum)

'now tidy up
Set rngGroupingIDs = Nothing
wsOutsideFile.Parent.Close SaveChanges:=False
End Sub

..it all depends what you want to do with the data in the sheet, but this
should get you started.

Cheers,
Dave

"RSteph" wrote:

I've got a spreadsheet that has a list of items and the grouping that they
belong to. I also have a .csv file that I open with excel and turn into a
spreadsheet. I'm making a macro to do some formatting/calculating/etc. within
the excel sheet made from the .csv.

What I would like to do is read in the values from the other excel file. The
file that I've got now has a list of items and quantities. I want to read in
the outside file (2 column file), to read off and insert the grouping ID's,
and to do some comparrisons with.

How would I go about reading in the file, and pointing to a column and/or
row within the list?

--

Dave Peterson

Ok, how I missed that I'll never know... thank you.

"Dave Peterson" wrote:

You dropped "Set" from the line. Put it back.

RSteph wrote:

I'm having a problem with the line:
Set wsOutsideFile = Application.Workbooks.Open("C:\myfile.xls").Worksh eets(1)

I've changed the C:\myfile.xls to the path and file name of the file I want
to open. It opens the file, but then immediately crashes with the error:

"Object Variable or With Block Variable Not set."

I've put in some MsgBox "Test" values to check where exactly the process
fails. It opens the file, but then crashes before it gets to the next line.
So It's got to be crashing there.

here's the line in my code:

Sub getGroups()
'This method will open the external .xls file and read in the DFR Groups.

Dim ofFile As Worksheet 'Create a variable to hold the worksheet.
Dim lastRow As Long 'Used to hold the value of last row on the sheet.
Dim i As Integer 'Used to incriment For Loop.
Dim x As Integer 'Used to incriment the row count.
Dim dfr As String 'Used to hold the dfr name. (Group Name)

MsgBox "Test 1"

ofFile = Application.Workbooks.Open("c:\item
grouping.xls").Worksheets(1) 'Open the workbook as ofFile.
MsgBox "Test 2"
lastRow = ofFile.Cells(Rows.Count, "B").End(xlUp).Row 'Get the last row
on the page.
MsgBox "Test 3"
x = 3 'Set the x value to 3.
MsgBox "Test 4"
dfr = "" 'Set the dfr string to empty.

MsgBox "Test 5"

'Scroll through the list of Groups, get the first group, then everytime
the group name
'changes after that, put in the new grouplist.
For i = 1 To lastRow 'Loop through to the end of the page, from bottom up.
'If "dfr" does not equal "ofFile.Cells(i,"B").Value" then do the
following, otherwise do nothing.
If dfr < ofFile.Cells(i, "B").Value Then
dfr = ofFile.Cells(i, "B").Value 'Set dfr to the name in the cell.
Cells(x, "G").Value = dfr 'Set the specified cell in the invList
workbook to the string dfr.
x = x + 1 'Incriment x.
End If
Next i 'Increment counter and reloop

End Sub 'End sub getGroups()

"Dave Ramage" wrote:

Something like this:

Sub Demo1()
Dim wsOutsideFile as Worksheet
Dim lLastRow as Long
Dim rngGroupingIDs as Range
Dim v as Variant
Dim strResult as String

'open file an set a pointer to the first worksheet
Set wsOutsideFile =
Application.Workbooks.Open("C:\myfile.xls").Worksh eets(1)

'''do something complicated
'find the last row in the list
lLastRow = wsOutsideFile.Range("A65536").End(xlUp).Row
'set range object to point to Grouping ID columns (assume column A and B,
'starting in cell A1)
Set rngGroupingIDs = wsOutsideFile.Range("A1").Resize(lLastRow,2)
'now do something with this range of data
'e.g. lookup value in column 1 and return coresponding value in column 2
v= Application.Match("MyLookupID", rngGroupingIDs.Columns(1),0)
If IsError(v) Then
Msgbox "Value not found!"
Else
strResult = rngGroupingIDs.Cells(v,2).Formula
MsgBox "Result found: " & strResult
End If

'''or maybe just return the value from a pecified row/column
MsgBox wsOutsideFile.Range("B3").Value
MsgBox wsOutsideFile.Cells(3,2).Value 'Cells(RowNum,ColumnNum)

'now tidy up
Set rngGroupingIDs = Nothing
wsOutsideFile.Parent.Close SaveChanges:=False
End Sub

..it all depends what you want to do with the data in the sheet, but this
should get you started.

Cheers,
Dave

"RSteph" wrote:

I've got a spreadsheet that has a list of items and the grouping that they
belong to. I also have a .csv file that I open with excel and turn into a
spreadsheet. I'm making a macro to do some formatting/calculating/etc. within
the excel sheet made from the .csv.

What I would like to do is read in the values from the other excel file. The
file that I've got now has a list of items and quantities. I want to read in
the outside file (2 column file), to read off and insert the grouping ID's,
and to do some comparrisons with.

How would I go about reading in the file, and pointing to a column and/or
row within the list?

--

Dave Peterson