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
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