Update Sheet From CSV Problem In Excel 2007

[___]

Morning / Evening All,

I'm having a small problem while trying to update my 'products' sheet from an external .csv file in Excel 2007. I'm using the following code which works fine in Excel 2000...

Sub openProductcsv()
On Error Resume Next

If ping("***.***.***.***") Then

    Application.DisplayAlerts = False
          
        If Not ThisWorkbook.ProtectStructure Then
          
              Sheets("products").Delete
 
        End If
   
    Workbooks.Open Filename:="http://mydomain.co.uk/orders/data/products.csv"

    ActiveSheet.Move Before:=Workbooks("myworkbook.xls").Sheets(1)
    
Else
    
    MsgBox "Cannot connect to server. Please make sure you have internet access or use the 'Save Order For later Upload' button."
    
End If

    Application.DisplayAlerts = True

End Sub

The error message I'm getting is...

Run-time error '1004'

Excel cannot insert the sheets into the destination workbook because it contains fewer rows and columns than the source workbook, you can select the data and then use Copy and Paste commands to insert it into the sheets of another workbook.

I'm guessing that this is because Excel 2007 has many more columns and rows than 2000 but I'm not sure of the best way to resolve the problem.

Any help much appreciated.

 

[Richard Schollar]

Hi

Rather than physically move the sheet you could just copy the values:

Sub openProductcsv()
On Error Resume Next
 
If ping("***.***.***.***") Then
 
    Application.DisplayAlerts = False
 
        If Not ThisWorkbook.ProtectStructure Then
 
              Sheets("products").Delete
 
        End If
 
    Workbooks.Open Filename:="http://mydomain.co.uk/orders/data/products.csv"
 
    ActiveSheet.UsedRange.Copy
    Workbooks("myworkbook.xls").Worksheets.Add.Paste
 
Else
 
    MsgBox "Cannot connect to server. Please make sure you have internet access or use the 'Save Order For later Upload' button."
 
End If
 
    Application.DisplayAlerts = True
 
End Sub

 

[___]

Hi Richard and thanks for your quick reply.

I have just tried your solution which works fine in both 2000 and 2007, the only problem is it doesn't close the .csv file after pasting and pastes to sheet1 which it inserts next to the last active sheet. Is there any way it could insert a sheet called 'products' in position one of the tabs then paste the data and close the .csv?

Once again, many thanks for your help.

 

[Richard Schollar]

Yep - try this:

Sub openProductcsv()
Dim wb As Workbook, ws As Worksheet
On Error Resume Next
 
If ping("***.***.***.***") Then
 
    Application.DisplayAlerts = False
 
        If Not ThisWorkbook.ProtectStructure Then
 
              Sheets("products").Delete
 
        End If
 
    Set wb = Workbooks.Open(Filename:=http://mydomain.co.uk/orders/data/products.csv)
 
    wb.Sheets(1).UsedRange.Copy
    With Workbooks("myworkbook.xls")
        Set ws =  .Worksheets.Add(Before:=.Sheets(1))
     End With
     ws.Paste
     wb.Close SaveChanges:=False
   
 
Else
 
    MsgBox "Cannot connect to server. Please make sure you have internet access or use the 'Save Order For later Upload' button."
 
End If
 
    Application.DisplayAlerts = True
 
End Sub

 

[___]

Great! thank you!

I was getting a syntax error on line

Set wb = Workbooks.Open(Filename:=http://mydomain.co.uk/orders/data/products.csv)

but changed it to

Set wb = Workbooks.Open(Filename:="http://lucylocketmail.co.uk/orders/data/products.csv")

and all works!

Still a slight issuse though, I rename the sheet as follows...

    wb.Sheets(1).UsedRange.Copy
    With Workbooks("myworkbook.xls")
        Set ws = .Worksheets.Add(Before:=.Sheets(1))
     End With
     ws.Paste
     wb.Close SaveChanges:=False
     Sheets("Sheet1").Name = "products"
     Range("A1").Select

which works fine unless I fire the code a second time where it inserts the sheet as Sheet2, and doesn't rename it to 'products'.

Any ideas?

Thanks again for your time and help.

 

[Richard Schollar]

In the adjusted code, I set an object reference (ws) to be the newly added sheet, so you can use this to rename it:

 wb.Sheets(1).UsedRange.Copy
    With Workbooks("myworkbook.xls")
        Set ws = .Worksheets.Add(Before:=.Sheets(1))
     End With
     ws.Paste
     wb.Close SaveChanges:=False
     ws.Name = "products"
     ws.Range("A1").Select

 

[___]

Perfect! That did the trick!

Many thanks once again for your help.