Not getting the object structure of VBA Excel
 

The problem I am having is with the object structure of Excel.

For instance I import some data into the first sheet, then I try to
delete the first column, since I don't need the data, but it doesn't see
to work this way.

Private Sub Workbook_Open()

....
code to load in some data..
....

'then I would like to delete the extraneous data with something like

Sheets(1).Columns(1).delete

'but this doesn't seem to be the way it's done.

Can I only work with selections, or do I have to set the object
reference for the function?

Any sites or advice appreciated.

Paul

Not getting the object structure of VBA Excel
 

Try

ThisWorkbook.Sheets(1).Columns(1).delete

--
Regards,
Tom Ogilvy


"Paul" wrote in message
news:dQaKd.11226$rw.5302@fed1read04...
The problem I am having is with the object structure of Excel.

For instance I import some data into the first sheet, then I try to
delete the first column, since I don't need the data, but it doesn't see
to work this way.

Private Sub Workbook_Open()

...
code to load in some data..
...

'then I would like to delete the extraneous data with something like

Sheets(1).Columns(1).delete

'but this doesn't seem to be the way it's done.

Can I only work with selections, or do I have to set the object
reference for the function?

Any sites or advice appreciated.

Paul

Not getting the object structure of VBA Excel
 

Tom Ogilvy wrote:
Try

ThisWorkbook.Sheets(1).Columns(1).delete

Thanks Tom, it helps when I start at the top of the hierarchy. Side
note, any in sight as to why the code completion hints quit after
...Sheets(1).

Paul

Not getting the object structure of VBA Excel
 

I don't understand the question and the only code you provided was the one
line.

I don't see how the workbook_open event fits into this scenario you
describe:

For instance I import some data into the first sheet, then I try to
delete the first column, since I don't need the data,

Seems like deleting a column would occur after the workbook_open had
completed.

--
Regards,
Tom Ogilvy


"Paul" wrote in message
news:25bKd.11230$rw.7321@fed1read04...
Tom Ogilvy wrote:
Try

ThisWorkbook.Sheets(1).Columns(1).delete

Thanks Tom, it helps when I start at the top of the hierarchy. Side
note, any in sight as to why the code completion hints quit after
..Sheets(1).

Paul

Not getting the object structure of VBA Excel
 

Tom Ogilvy wrote:
I don't understand the question and the only code you provided was the one
line.

I don't see how the workbook_open event fits into this scenario you
describe:


For instance I import some data into the first sheet, then I try to
delete the first column, since I don't need the data,


Seems like deleting a column would occur after the workbook_open had
completed.


Tom,

I am new to programming excel, please forgive my ignorance. Basically
what I am trying to do is load data into a work sheet from a website.
After the data is loaded, I intend to then reformat it, delete un needed
data columns, convert meters to feet etc. My thought is to do all this
in the workbook open event, to use that as the master control for the
whole program. Below is my code so far, I think this will give you a
better perspective on what's going on

Private Sub Workbook_Open()

Dim mMonth
Dim mDay
Dim mYear


mYear = year(FormatDateTime(Now, vbShortDate))
mMonth = month(FormatDateTime(Now, vbShortDate))
mDay = day(FormatDateTime(Now, vbShortDate))

Sheet1.Columns.Clear

Dim dinky As QueryTable
mConnectionString =
"URL;http://weather.uwyo.edu/cgi-bin/sounding?region=naconf&TYPE=TEXT%3ALIST&"
mConnectionString = mConnectionString +
"YEAR=2005&MONTH=01&FROM=2700&TO=2700&STNM=723 65"

Set dinky =
Sheet1.QueryTables.Add(mConnectionString,Applicati on.Range("A2:Z2"))

While dinky.Refreshing
'do nothing
Wend

With dinky

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.Refresh

End With

'once data is loaded into the page, start cleaning it up i.e.
'ThisWorkbook.Sheets(1).Columns(1).delete

End Sub

Not getting the object structure of VBA Excel
 

I have modified it to accept arguments to the connection string so you get
Today's data.
I also set the Backgroundquery = False parameter so the query is completed
before your code continues so you don't need the While . . . Wend loop.

Sub Workbook_Open()

Dim mMonth As String
Dim mDayStart As String
Dim mDayEnd As String
Dim mYear As String
Dim mConnectionString As String
Dim dinky As QueryTable

mYear = Format(Year(Now), "0000")
mMonth = Format(Month(Now), "00")
mDayEnd = Format(Day(Now), "00") & "00"
mDayStart = Format(Day(Now), "00") & "00"
Sheet1.Columns.Clear

mConnectionString = "URL;http://weather.uwyo.edu/" & _
"cgi-bin/sounding?region=naconf&TYPE=TEXT%3ALIST&"
mConnectionString = mConnectionString & _
"YEAR=" & mYear & _
"&MONTH=" & mMonth & _
"&FROM=" & mDayStart & _
"&TO=" & mDayEnd & _
"&STNM=72365"
' Debug.Print mConnectionString

Set dinky = Sheet1.QueryTables.Add(mConnectionString,
Application.Range("A2"))

With dinky
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.BackgroundQuery = False
.Refresh
End With

Sheet1.Columns(1).Delete
End Sub


--
Regards,
Tom Ogilvy


"Paul" wrote in message
news:gFbKd.11232$rw.9027@fed1read04...
Tom Ogilvy wrote:
I don't understand the question and the only code you provided was the
one
line.

I don't see how the workbook_open event fits into this scenario you
describe:


For instance I import some data into the first sheet, then I try to
delete the first column, since I don't need the data,


Seems like deleting a column would occur after the workbook_open had
completed.


Tom,

I am new to programming excel, please forgive my ignorance. Basically
what I am trying to do is load data into a work sheet from a website.
After the data is loaded, I intend to then reformat it, delete un needed
data columns, convert meters to feet etc. My thought is to do all this
in the workbook open event, to use that as the master control for the
whole program. Below is my code so far, I think this will give you a
better perspective on what's going on

Private Sub Workbook_Open()

Dim mMonth
Dim mDay
Dim mYear


mYear = year(FormatDateTime(Now, vbShortDate))
mMonth = month(FormatDateTime(Now, vbShortDate))
mDay = day(FormatDateTime(Now, vbShortDate))

Sheet1.Columns.Clear

Dim dinky As QueryTable
mConnectionString =

"URL;http://weather.uwyo.edu/cgi-bin/soun...PE=TEXT%3ALIST
&"
mConnectionString = mConnectionString +
"YEAR=2005&MONTH=01&FROM=2700&TO=2700&STNM=723 65"

Set dinky =
Sheet1.QueryTables.Add(mConnectionString,Applicati on.Range("A2:Z2"))

While dinky.Refreshing
'do nothing
Wend

With dinky

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.Refresh

End With

'once data is loaded into the page, start cleaning it up i.e.
'ThisWorkbook.Sheets(1).Columns(1).delete

End Sub