Remove blank space at the beginning of a cell

[bn1974]

I download an excel spreadsheet from an outside source, and it comes in with a space before the data in column A. I need to remove this space before I import into my database.

I used this code in another function, and it worked perfectly. For some reason, I am getting an "Invalid Use of Null" error this time?

Function RemoveSpaceBeforeWellLicense()
Dim oExcel As Object
    Dim oBook As Object
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    Set oBook = oExcel.Workbooks.Open("S:\Renée\Database downloads\AER\AER Suspended Well List.xls")
  AutoFilterMode = False
With oBook.Sheets(1).Range("A2", oBook.Sheets(1).Range("A" & oBook.Sheets(1).Rows.Count).End(xlUp))
[COLOR=red].Value = VBA.Replace(.Text, " ", "")  ERROR HERE[/COLOR]
End With
oBook.Save
oBook.Close
oExcel.Quit
End Function[CODE]

[/CODE]

 

[Ranman256]

.Value = trim(.Text)

 

[bn1974]

That deleted all of the data in the cell?

 

[Ranman256]

it only removes spaces.

 

[CJ_London]

unless .text doesn't have a value

 

[Galaxiom]

Not related to your problem but a word or two of advice. It is an almost universal bad practice because all the Microsoft documentation and advice at virtually every Excel help site make the same mistake.

oBook.Sheets(1).Range...

Referencing sheets via their index in code is completely vulnerable to being broken. Sheets(1) changes when the order of sheets is changed. You can use Sheets("sheetname") instead but that will break if the name of the sheet is edited.

It is a far better practice to use the sheet's object name.

oBook.sheetobjectname.Range......

The object name is the one shown in the VBA Project Explorer window. It is the only place you can see it. The object name is the name before the displayed sheet name shown in parentheses.

It defaults to the same as the sheet name when it is first created but can be changed via the Properties window. Giving it a meaningful name will make it easier to write code. It won't break code unless someone edits the object name, which is practically impossible if the code is password protected.

Similarly using Range("A1") references. These will break or worse, return the wrong value, if rows or columns are inserted. Where possible, always use Named Ranges where the range is referred to in code.

Furthermore, be aware that entering the range names using the box on the formula bar will create a name with a workbook scope. Keeping track of the names and avoiding name conflicts can become very messy in a big workbook. Instead, use Define Name command in the Formulas Ribbon. This provides an option to set the scope of the name to either the whole workbook or a particular worksheet.

Names with a worksheet scope must be referred to in the context of the worksheet.

 

[bn1974]

Thanks very much Galaxiom. Appreciate the tips. ☺
Still haven't figured out my original issue. LOL

 

[CJ_London]

have you tried

.Value = trim(.Value)

what is the data? text or numeric? if the latter try

.Value = val(.Value)