PDA

View Full Version : Solved: Import of csv file fails in 2007 only



JimS
04-14-2010, 04:23 PM
I'm opening a csv file and doing a copy of it and pasting it into a workbook. Below is a portion of the code that opens the csv file and does the copy/paste.

This routine works fine in Excel 2003 but fails in 2007. The really strange thing is that it works for 90% of the files that I'm "importing" with 2007, but fails everytime on one certain file.

I'm thinking there must be something worng in the csv file but with hundreds of rows and 50+ columns I have no way of determining what might be wrong with the file.

It looks like it fails at the "SourceBook.Close False" line.

Anyone have any ideas why this line might fail?

Thanks...

Jim




Sub ImportDiskTextFile()
Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

Application.ScreenUpdating = False

Sheets("Data").Visible = True
Sheets("Data").Select

' Added to clear Input Sheet before Import
Cells.Select
Selection.ClearContents
Range("A1").Select

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

MsgBox "Click OK and navigate to the 'Open.csv' file that you just exported out of the eRoom Database during Step 3.", , "Commercial Project Management Database Import Module - Read Completely"
' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.csv")
' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Add a Message Prompt
Prompt1 = "Importing eRoom CSV file Step 1 of 6 - Standby"
Application.StatusBar = Prompt1


' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook
' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
Sheets("Data").Select
DestCell.PasteSpecial Paste:=xlPasteFormats
DestCell.PasteSpecial Paste:=xlValues
Range("A1").Select

' Close the book containing the text file.
Application.DisplayAlerts = False
SourceBook.Close False
Application.DisplayAlerts = True

Prompt2 = "Importing eRoom CSV file Step 2 of 6 - Standby"
Application.StatusBar = Prompt2


Run ("StripHTML5")


Prompt3 = "Importing eRoom CSV file Step 3 of 6 - Standby"
Application.StatusBar = Prompt3


Run ("CleanData")


Prompt4 = "Importing eRoom CSV file Step 4 of 6 - Standby"
Application.StatusBar = Prompt4

If Range("Type").Value = "PDM Pivots" Then
Run ("BuildPivot")
GoTo 10
End If


Prompt5 = "Importing eRoom CSV file Step 5 of 6 - Standby"
Application.StatusBar = Prompt5

Sheets("DM Report").Visible = True
DistrictSelector.Show


10 Prompt6 = "Importing eRoom CSV file Step 6 of 6 - Standby"
Application.StatusBar = Prompt6

Sheets("Start Here").Visible = False


' Clear the Message Prompt
Application.StatusBar = False

Application.ScreenUpdating = False

End Sub

mdmackillop
04-14-2010, 04:26 PM
Maybe

SpecialCells(xlCellTypeLastCell)

JimS
04-14-2010, 05:20 PM
Not sure if this helps but the error is a Run-time error'-2147352567 (80020009)': Method "Run of object'_Global' failed.

I tried your suggestion but it still failed.

JimS
04-14-2010, 05:37 PM
It appears that if I rem out this line it works everytime.

'Application.DisplayAlerts = False

I click on No when asked about deleting the large amount of information from the Clipboard.

If I click on Yes to save the information to the Clipboard it fails.

So I addded this line after the 2 DestCell.PasteSpecials

Application.CutCopyMode = False