Import from web page - dates and numbers import as text

[jez6363]

Hi

I have a web page that is a tab delimeted text file, basically a lot of incoming orders. I want to read this into an orders spreadsheet.

I have it reading the data in, but all dates and numbers are being imported as text.

I have read 100 solutions to this, but none of them seem to work. I have tried paste and pastespecial and changing numberformats, but nothing seems to work.

I don't want to have to make the vba know about the format of each column and fix it up. When I just use cut and paste from the browser, and do a Paste special, paste as text, Excel gets all the data as proper dates and numbers etc.

The macro I am using is here:

Sub ImportOrders()
'
' ImportOrders Macro
' Import Orders
'
' Keyboard Shortcut: Ctrl+o
'
       
    varResponse = MsgBox("Proceed and import orders? 'No' or 'Yes'", vbYesNo + 256, "Proceed and import orders?")
    If varResponse <> vbYes Then Exit Sub
    
    sheetname = ActiveSheet.Name
    workbookname = ActiveWorkbook.Name

    Workbooks.Open Filename:= _
        "http://www.mywebsite.com/ipn.php5"
    
    'Search for any entry, by searching backwards by Rows.
    LastRow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row

    Range("A1", "U" & LastRow).Select

    Selection.Copy
    
    ' Switch to original sheet
    Windows(workbookname).Activate
    Worksheets(sheetname).Select
    
    ' Move to 3 lines past end of spreadsheet
    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Cells(ix + 3, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ' ActiveSheet.Paste
    
    Application.CutCopyMode = False
    
    Workbooks("ipn.php5").Close SaveChanges:=False

End Sub

Main problem is that the dates and numbers are text, which messes up calculations later on.

Secondary problem is that I have to know the width of the incoming data (cols A to U) - it would be better if it didn't have to know that.

Please help!!!
Thanks, Jeremy

 

[CWatts]

There are several ways to convert your text dates to real dates. One way is to perform some arithmetic on the dates to convert them, so you write a 1 in a random cell and then copy it.

Then highlight all your dates and paste special > Multiply.

A VBA option would be like below. I'm not as adept at using ranges in VBA so I'll defer to someone else's expertise for that one.

Sub TextDate()
    Range("A1").Value = DateValue(Range("A1").Value)
    Range("A1").NumberFormat = "m/d/yy"
End Sub

 

[jez6363]

Hi

Well, its getting worse. Such a simple thing, to do by hand, is a nightmare in VBA.

The data I am pasting contains order lines, and sometimes exception lines. e.g.

----------------------------------------------
01/07/2011 0.00 39.99 fred smith
01/07/2011 0.00 $57.00 john doe

*** The next order needs special handling

01/07/2011 0.00 $57.00 peter jackson
12/07/2011 0.00 CA$57.00 amy jones
----------------------------------------------

I have tried looping through all the data, fixing up each field - I've only got it working correctly for dates.

When I try that for the numbers, it goes wrong. It seems as if during my original read into the Excel temporary sheet, it is losing the $ information from the lines that have. In the temporary spreadsheet Excel creates from the read they have a £ symbol in front of them, if the original data had a $. So it manages to read the data as numbers, but paste it as raw text by default - its brilliant at making the wrong default choices at every single turn...

The code I now have is:

    Workbooks.Open Filename:= _
        "mywebsite.com/ipn.php5"
    
    'Search for any entry, by searching backwards by Rows.
    LastRow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row

    For Each c In ActiveSheet.Range("A1", "A" & LastRow).Cells
        If IsDate(c.Value) Then
            c.Value = DateValue(c.Value)
        End If
        c.NumberFormat = "dd/mm/yyyy"
    Next
    For Each c In ActiveSheet.Range("B1", "B" & LastRow).Cells
        If c.Value <> "" Then
            c.Value = c.Value + 0
        End If
        c.NumberFormat = "0.00"
    Next
    For Each c In ActiveSheet.Range("C1", "C" & LastRow).Cells
        If c.Value <> "" Then
            c.Value = c.Value + 0
        End If
        c.NumberFormat = "0.00"
    Next
    
    Range("A1", "U" & LastRow).Select

    Selection.Copy
    
    ' Switch to original sheet
    Windows(workbookname).Activate
    Worksheets(sheetname).Select
    
    ' Move to 3 lines past end of spreadsheet
    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Cells(ix + 3, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

All this is turning a simple task into a nightmare. To do it manually requires simply to load the txt file in my web browser, copy the lines, and then do a Paste Special Text, into the spreadsheet. That just does everything correctly.

When I do it via VBA its a complicated mess.

I am amazed Excel is so bad at this sort of basic task. It makes easy things very difficult, for no apparent reason other than very bad choices about how to handle things. Same as having to round every sum you do, because they decided to treat numbers as scientific, though in practise 99% of spreadsheets are used for money, which is simply an integer multipled by 100.

Is there a way to make VBA just do what I do by hand, when I copy the text and just do a Paste Special and paste as text?

Thanks, Jeremy

 

[jez6363]

Hi,

Well, I have got it working. In the end I used the OpenText way of reading a file, and defined the type of each column as text. That preserved all the formatting in the original data. The code is below in case it helps anyone else:

    sheetname = ActiveSheet.Name
    workbookname = ActiveWorkbook.Name

    Dim ColumnsDesired
    Dim DataTypeArray
    Dim x
    Dim ColumnArray(0 To 20, 1 To 2)
    Dim ColumnCount
    
    ColumnCount = 21
    If ColumnCount > 25 Then
        MsgBox ("Trying to import too many columns")
    End If
    
    ' populate the array for fieldinfo
    For x = 0 To ColumnCount - 1
        ColumnArray(x, 1) = x + 1
        ColumnArray(x, 2) = xlTextFormat
    Next x
    ColumnArray(0, 2) = xlDMYFormat
    
    Workbooks.OpenText Filename:= _
        "http://www.mywebsite.com/ipn.php5", _
        DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray
    
    'Search for any entry, by searching backwards by Rows.
    LastRow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row

    'For Each c In ActiveSheet.Range("A1", "A" & LastRow).Cells
    '    If IsDate(c.Value) Then
    '        c.Value = DateValue(c.Value)
    '    End If
    '    c.NumberFormat = "dd/mm/yyyy"
    'Next
    For Each c In ActiveSheet.Range("B1", "B" & LastRow).Cells
        If c.Value <> "" Then
            If (IsNumeric(c.Value)) Then
                c.Value = c.Value + 0
            End If
        End If
        c.NumberFormat = "0.00"
    Next
    For Each c In ActiveSheet.Range("C1", "C" & LastRow).Cells
        If c.Value <> "" Then
            If (IsNumeric(c.Value)) Then
                c.Value = c.Value + 0
            End If
        End If
        c.NumberFormat = "0.00"
    Next
    
    Range("A1", "U" & LastRow).Select

    Selection.Copy
    
    ' Switch to original sheet
    Windows(workbookname).Activate
    Worksheets(sheetname).Select
    
    ' Move to 3 lines past end of spreadsheet
    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Cells(ix + 3, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ' ActiveSheet.Paste
    
    Application.CutCopyMode = False
    
    Workbooks("ipn.php5").Close SaveChanges:=False