Urgent!

[fkheng]

I need to modify locations of data in an excel worksheet!

For example, I am utilising 4 columns (A,B,C,D)
In row 1, I have A storing "Test1", B storing "Test2"
On the 2nd row, I have C storing "Test3", D storing "Test4"

When I execute my VB code, the data in row 1 column B has to be moved below A, and D below C.

How do I modify locations? And how should i access the worksheet? Any article which can show me how to pin point hte locations?

[alex_read]

In it's simplest form, this is the general idea to do this:

Private Sub CommandButton1_Click()
    Dim rngCopyRange As Range
    Dim rngPasteRange As Range
    
    rngCopyRange = Range("B1").Value
    rngPasteRange = Range("A2").Value
    
    rngPasteRange = rngCopyRange
    rngCopyRange = ""
End Sub

You can cell on the range object & specify Letter & number characters to rference 1 or more cells ("A1", "A1:B5" etc.)

Private Sub CommandButton1_Click()
    Range("B1").Value = Range("A2").Value
    Range("A2").Value = ""
End Sub

OR...

You can call on the cells object & specify a numerical value for the row, then another number for the column - so cells(1,1) would be the same as range("A1") if that makes sense:

Private Sub CommandButton1_Click()
    Cells(2, 1).Value = Cells(1, 2).Value
    Cells(1, 2).Value = ""
End Sub

[alex_read]

For the last point there, to specify a worksheet you can either call on the sheet id, or the sheet name:

Msgbox Worksheets(1).Range("A1").value

Or

Msgbox Worksheets("Sheet1").Range("A1").value

Will give you the same result. I would normally select the worksheet I want to work with first, then call on the range etc. objects without putting the worksheets(1). call before them. i.e. -

Private Sub CommandButton1_Click()
    Worksheets(1).select
 
    Range("B1").Value = Range("A2").Value
    Range("A2").Value = ""
End Sub

[fkheng]

thanx, very helpful, will try and see how it goes...

i know how to create an object for excel worksheets and save 'em...but how do i open an existing file, not as in physically open it, but i want to gain access to it to perform the aforementioned solutions onto the problem...

[alex_read]

When you first create your excel object, use the following:

objXLApp.Workbooks.add "C:\Path\MyFile.xls"

Where the objXLApp is your created Excel.Application object reference. Hope this helps!

[fkheng]

is there a way of detecting the end of an excel worksheet
say i want to check row by row, how do i know when to stop?

[alex_read]

Suppose these should all really go into new posts, but what the hell. Okay there's an Excel object called 'UsedRows' which is incredibly useful for this!

You can call on the rows.count function to retreive the last row which has an entry in it (note thi starts at 0):
MsgBox ActiveSheet.UsedRange.Rows.Count +1

The columns.count function to find the last column with an entry in:
MsgBox Worksheets(1).UsedRange.Columns.Count

Or you can retrieve the actual cell reference of the last cell which has an entry in bu using the SpecialCells call:
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address

[fkheng]

yeah, but this assumes i have to have defined a range earlier on...
anyway working without a range?
say my range would be the whole document, until the row where there is no more data...is that possible?

[fkheng]

this is just a snip of my code so far...

Dim path As String
Dim workSheetName As String
Dim oApp As Excel.Application
Dim oWrkBk As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim intRow As Integer
Dim intCol As Integer

oApp = CreateObject("Excel.Application")
oApp.Visible = False
path = App.path & "\testSheet.xls"
oWrkBk = oApp.Workbooks.Add(path)


i get an error msg stating "Invalid use of property" with this line :

oApp = CreateObject("Excel.Application")

wat's wrong with it ?

[fkheng]

okay, fixed that problem...
i think i was using vb.net syntax not vb 6

i have a code to saveas a file...
wat if i dun want the confirmation dialog box to replace a file to pop up, is there a way to just forced the overwrite?

wat about deleting a file?

[alex_read]

okay point 1 - lookup the Displayalerts function to supress all of Excel's warning messages:

objXLApp.Displayalerts = false 
    ' Save Code Here
objXLApp.Displayalerts = true

Point 2 - you need the kill statement to delete a file:

Kill "C:\Path\MyXLFile.xls"