Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cant pass array to excel from c++ using xloper structure | Excel Worksheet Functions | |||
excel outlines structure | Excel Discussion (Misc queries) | |||
Protect an Excel 2007 workbook structure | Excel Discussion (Misc queries) | |||
Analyze Excel files structure | Excel Programming | |||
Analyze Excel files structure | Excel Programming |