I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
website, Find a specfic date in column A and determine row in order to get
the specific data I need from the sheet. Spreadsheet opens and I get the
"91" error which I think because it can't find that date. But when I copy
the spreadsheet to my local harddrive and run the code again, it works.
I have verified that the correct formatted date is in the GetDate field by
using msgbox prior to the FIND stmt.

Sub GET_ONTDEMAND()
On Error GoTo Errorhandler

Dim GetDate As Date
Dim rowVal As Integer
Dim FileDate As String
Dim formatdate As String
Dim myrange As Range

FileDate = Range("B2").Value
GetDate = Range("c2").Value
formatdate = Format([GetDate], "dd.mmm.yy")



'Workbooks.Open Filename:="c:\\zonaldemands.csv"
'Windows("ZonalDemands.csv").Activate

Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
FileDate & ".csv"
Windows("ZonalDemands_" & FileDate & ".csv").Activate
Range("a2").Select

Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not myrange Is Nothing Then myrange.Activate
rowVal = ActiveCell.Row
Range("A" & rowVal, "G" & rowVal + 167).Select
Selection.Copy
Windows("Shadow Pricing 2007.xls").Activate
Range("a5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("g5:g200").Copy
Range("d5").PasteSpecial
Range("e5:g200").ClearContents
Range("a1").Select
Workbooks("http://website.ca/ZonalDemands_" & FileDate &
".csv").Close SaveChanges:=False


Exit Sub
Errorhandler:

MsgBox (Err.Number & ", " & Err.Description)


End Sub

When I change "formatdate" in the statement below to "10-Nov-07" I don't get
the error message and all is fine

Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)



"DianeA" wrote:

> I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
> website, Find a specfic date in column A and determine row in order to get
> the specific data I need from the sheet. Spreadsheet opens and I get the
> "91" error which I think because it can't find that date. But when I copy
> the spreadsheet to my local harddrive and run the code again, it works.
> I have verified that the correct formatted date is in the GetDate field by
> using msgbox prior to the FIND stmt.
>
> Sub GET_ONTDEMAND()
> On Error GoTo Errorhandler
>
> Dim GetDate As Date
> Dim rowVal As Integer
> Dim FileDate As String
> Dim formatdate As String
> Dim myrange As Range
>
> FileDate = Range("B2").Value
> GetDate = Range("c2").Value
> formatdate = Format([GetDate], "dd.mmm.yy")
>
>
>
> 'Workbooks.Open Filename:="c:\\zonaldemands.csv"
> 'Windows("ZonalDemands.csv").Activate
>
> Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
> FileDate & ".csv"
> Windows("ZonalDemands_" & FileDate & ".csv").Activate
> Range("a2").Select
>
> Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False)
> If Not myrange Is Nothing Then myrange.Activate
> rowVal = ActiveCell.Row
> Range("A" & rowVal, "G" & rowVal + 167).Select
> Selection.Copy
> Windows("Shadow Pricing 2007.xls").Activate
> Range("a5").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("g5:g200").Copy
> Range("d5").PasteSpecial
> Range("e5:g200").ClearContents
> Range("a1").Select
> Workbooks("http://website.ca/ZonalDemands_" & FileDate &
> ".csv").Close SaveChanges:=False
>
>
> Exit Sub
> Errorhandler:
>
> MsgBox (Err.Number & ", " & Err.Description)
>
>
> End Sub
>
>

If you step through the code what is the value of formatdate when it goes to
do the find?
--
HTH...

Jim Thomlinson


"DianeA" wrote:

> When I change "formatdate" in the statement below to "10-Nov-07" I don't get
> the error message and all is fine
>
> Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False)
>
>
>
> "DianeA" wrote:
>
> > I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
> > website, Find a specfic date in column A and determine row in order to get
> > the specific data I need from the sheet. Spreadsheet opens and I get the
> > "91" error which I think because it can't find that date. But when I copy
> > the spreadsheet to my local harddrive and run the code again, it works.
> > I have verified that the correct formatted date is in the GetDate field by
> > using msgbox prior to the FIND stmt.
> >
> > Sub GET_ONTDEMAND()
> > On Error GoTo Errorhandler
> >
> > Dim GetDate As Date
> > Dim rowVal As Integer
> > Dim FileDate As String
> > Dim formatdate As String
> > Dim myrange As Range
> >
> > FileDate = Range("B2").Value
> > GetDate = Range("c2").Value
> > formatdate = Format([GetDate], "dd.mmm.yy")
> >
> >
> >
> > 'Workbooks.Open Filename:="c:\\zonaldemands.csv"
> > 'Windows("ZonalDemands.csv").Activate
> >
> > Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
> > FileDate & ".csv"
> > Windows("ZonalDemands_" & FileDate & ".csv").Activate
> > Range("a2").Select
> >
> > Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> > lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False)
> > If Not myrange Is Nothing Then myrange.Activate
> > rowVal = ActiveCell.Row
> > Range("A" & rowVal, "G" & rowVal + 167).Select
> > Selection.Copy
> > Windows("Shadow Pricing 2007.xls").Activate
> > Range("a5").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Range("g5:g200").Copy
> > Range("d5").PasteSpecial
> > Range("e5:g200").ClearContents
> > Range("a1").Select
> > Workbooks("http://website.ca/ZonalDemands_" & FileDate &
> > ".csv").Close SaveChanges:=False
> >
> >
> > Exit Sub
> > Errorhandler:
> >
> > MsgBox (Err.Number & ", " & Err.Description)
> >
> >
> > End Sub
> >
> >

10-Nov-07

"Jim Thomlinson" wrote:

> If you step through the code what is the value of formatdate when it goes to
> do the find?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "DianeA" wrote:
>
> > When I change "formatdate" in the statement below to "10-Nov-07" I don't get
> > the error message and all is fine
> >
> > Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> > lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False)
> >
> >
> >
> > "DianeA" wrote:
> >
> > > I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
> > > website, Find a specfic date in column A and determine row in order to get
> > > the specific data I need from the sheet. Spreadsheet opens and I get the
> > > "91" error which I think because it can't find that date. But when I copy
> > > the spreadsheet to my local harddrive and run the code again, it works.
> > > I have verified that the correct formatted date is in the GetDate field by
> > > using msgbox prior to the FIND stmt.
> > >
> > > Sub GET_ONTDEMAND()
> > > On Error GoTo Errorhandler
> > >
> > > Dim GetDate As Date
> > > Dim rowVal As Integer
> > > Dim FileDate As String
> > > Dim formatdate As String
> > > Dim myrange As Range
> > >
> > > FileDate = Range("B2").Value
> > > GetDate = Range("c2").Value
> > > formatdate = Format([GetDate], "dd.mmm.yy")
> > >
> > >
> > >
> > > 'Workbooks.Open Filename:="c:\\zonaldemands.csv"
> > > 'Windows("ZonalDemands.csv").Activate
> > >
> > > Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
> > > FileDate & ".csv"
> > > Windows("ZonalDemands_" & FileDate & ".csv").Activate
> > > Range("a2").Select
> > >
> > > Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> > > lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > MatchCase:=False)
> > > If Not myrange Is Nothing Then myrange.Activate
> > > rowVal = ActiveCell.Row
> > > Range("A" & rowVal, "G" & rowVal + 167).Select
> > > Selection.Copy
> > > Windows("Shadow Pricing 2007.xls").Activate
> > > Range("a5").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > > Range("g5:g200").Copy
> > > Range("d5").PasteSpecial
> > > Range("e5:g200").ClearContents
> > > Range("a1").Select
> > > Workbooks("http://website.ca/ZonalDemands_" & FileDate &
> > > ".csv").Close SaveChanges:=False
> > >
> > >
> > > Exit Sub
> > > Errorhandler:
> > >
> > > MsgBox (Err.Number & ", " & Err.Description)
> > >
> > >
> > > End Sub
> > >
> > >

Hi,

If using the hard coded value of "10-Nov-07" works try adjusting your
format replacing the periods with dashes.

formatdate = Format([GetDate], "dd-mmm-yy")

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"DianeA" <(E-Mail Removed)> wrote in message
news:3811DEA6-C0A7-491A-93DF-(E-Mail Removed)...
> I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
> website, Find a specfic date in column A and determine row in order to get
> the specific data I need from the sheet. Spreadsheet opens and I get the
> "91" error which I think because it can't find that date. But when I copy
> the spreadsheet to my local harddrive and run the code again, it works.
> I have verified that the correct formatted date is in the GetDate field by
> using msgbox prior to the FIND stmt.
>
> Sub GET_ONTDEMAND()
> On Error GoTo Errorhandler
>
> Dim GetDate As Date
> Dim rowVal As Integer
> Dim FileDate As String
> Dim formatdate As String
> Dim myrange As Range
>
> FileDate = Range("B2").Value
> GetDate = Range("c2").Value
> formatdate = Format([GetDate], "dd.mmm.yy")
>
>
>
> 'Workbooks.Open Filename:="c:\\zonaldemands.csv"
> 'Windows("ZonalDemands.csv").Activate
>
> Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
> FileDate & ".csv"
> Windows("ZonalDemands_" & FileDate & ".csv").Activate
> Range("a2").Select
>
> Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False)
> If Not myrange Is Nothing Then myrange.Activate
> rowVal = ActiveCell.Row
> Range("A" & rowVal, "G" & rowVal + 167).Select
> Selection.Copy
> Windows("Shadow Pricing 2007.xls").Activate
> Range("a5").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("g5:g200").Copy
> Range("d5").PasteSpecial
> Range("e5:g200").ClearContents
> Range("a1").Select
> Workbooks("http://website.ca/ZonalDemands_" & FileDate &
> ".csv").Close SaveChanges:=False
>
>
> Exit Sub
> Errorhandler:
>
> MsgBox (Err.Number & ", " & Err.Description)
>
>
> End Sub
>
>

my answer may have been vague...

When i pass the value through the "formatdate" variable and do a check using
msgbox(formtdate) immediately prior to the find.. it has "10-Nov-07"

The A column in the spreadsheet cell that i'm looking in is formatted to
custom dd-mmm-yy


"DianeA" wrote:

> 10-Nov-07
>
> "Jim Thomlinson" wrote:
>
> > If you step through the code what is the value of formatdate when it goes to
> > do the find?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "DianeA" wrote:
> >
> > > When I change "formatdate" in the statement below to "10-Nov-07" I don't get
> > > the error message and all is fine
> > >
> > > Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> > > lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > MatchCase:=False)
> > >
> > >
> > >
> > > "DianeA" wrote:
> > >
> > > > I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
> > > > website, Find a specfic date in column A and determine row in order to get
> > > > the specific data I need from the sheet. Spreadsheet opens and I get the
> > > > "91" error which I think because it can't find that date. But when I copy
> > > > the spreadsheet to my local harddrive and run the code again, it works.
> > > > I have verified that the correct formatted date is in the GetDate field by
> > > > using msgbox prior to the FIND stmt.
> > > >
> > > > Sub GET_ONTDEMAND()
> > > > On Error GoTo Errorhandler
> > > >
> > > > Dim GetDate As Date
> > > > Dim rowVal As Integer
> > > > Dim FileDate As String
> > > > Dim formatdate As String
> > > > Dim myrange As Range
> > > >
> > > > FileDate = Range("B2").Value
> > > > GetDate = Range("c2").Value
> > > > formatdate = Format([GetDate], "dd.mmm.yy")
> > > >
> > > >
> > > >
> > > > 'Workbooks.Open Filename:="c:\\zonaldemands.csv"
> > > > 'Windows("ZonalDemands.csv").Activate
> > > >
> > > > Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
> > > > FileDate & ".csv"
> > > > Windows("ZonalDemands_" & FileDate & ".csv").Activate
> > > > Range("a2").Select
> > > >
> > > > Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> > > > lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > > > MatchCase:=False)
> > > > If Not myrange Is Nothing Then myrange.Activate
> > > > rowVal = ActiveCell.Row
> > > > Range("A" & rowVal, "G" & rowVal + 167).Select
> > > > Selection.Copy
> > > > Windows("Shadow Pricing 2007.xls").Activate
> > > > Range("a5").Select
> > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > > SkipBlanks _
> > > > :=False, Transpose:=False
> > > > Range("g5:g200").Copy
> > > > Range("d5").PasteSpecial
> > > > Range("e5:g200").ClearContents
> > > > Range("a1").Select
> > > > Workbooks("http://website.ca/ZonalDemands_" & FileDate &
> > > > ".csv").Close SaveChanges:=False
> > > >
> > > >
> > > > Exit Sub
> > > > Errorhandler:
> > > >
> > > > MsgBox (Err.Number & ", " & Err.Description)
> > > >
> > > >
> > > > End Sub
> > > >
> > > >

Sorry... my error... the date 10.Nov.07 is returned and the spreadsheet shows
10.Nov.07 in column A starting at row 2

"Andy Pope" wrote:

> Hi,
>
> If using the hard coded value of "10-Nov-07" works try adjusting your
> format replacing the periods with dashes.
>
> formatdate = Format([GetDate], "dd-mmm-yy")
>
> Cheers
> Andy
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "DianeA" <(E-Mail Removed)> wrote in message
> news:3811DEA6-C0A7-491A-93DF-(E-Mail Removed)...
> > I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
> > website, Find a specfic date in column A and determine row in order to get
> > the specific data I need from the sheet. Spreadsheet opens and I get the
> > "91" error which I think because it can't find that date. But when I copy
> > the spreadsheet to my local harddrive and run the code again, it works.
> > I have verified that the correct formatted date is in the GetDate field by
> > using msgbox prior to the FIND stmt.
> >
> > Sub GET_ONTDEMAND()
> > On Error GoTo Errorhandler
> >
> > Dim GetDate As Date
> > Dim rowVal As Integer
> > Dim FileDate As String
> > Dim formatdate As String
> > Dim myrange As Range
> >
> > FileDate = Range("B2").Value
> > GetDate = Range("c2").Value
> > formatdate = Format([GetDate], "dd.mmm.yy")
> >
> >
> >
> > 'Workbooks.Open Filename:="c:\\zonaldemands.csv"
> > 'Windows("ZonalDemands.csv").Activate
> >
> > Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
> > FileDate & ".csv"
> > Windows("ZonalDemands_" & FileDate & ".csv").Activate
> > Range("a2").Select
> >
> > Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
> > lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False)
> > If Not myrange Is Nothing Then myrange.Activate
> > rowVal = ActiveCell.Row
> > Range("A" & rowVal, "G" & rowVal + 167).Select
> > Selection.Copy
> > Windows("Shadow Pricing 2007.xls").Activate
> > Range("a5").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Range("g5:g200").Copy
> > Range("d5").PasteSpecial
> > Range("e5:g200").ClearContents
> > Range("a1").Select
> > Workbooks("http://website.ca/ZonalDemands_" & FileDate &
> > ".csv").Close SaveChanges:=False
> >
> >
> > Exit Sub
> > Errorhandler:
> >
> > MsgBox (Err.Number & ", " & Err.Description)
> >
> >
> > End Sub
> >
> >
>