Solved: Close Sheet after Copy

[mduff]

Hi I have this code to open and CSV file and then Copy the contents to an excel worksheet it's working fine the only issue is I cannot figure out how to have it close the CVS file after the data has been copied to the new sheet

Any help will be appreciated



Sub OpenFile()
Dim fileName
Dim ActiveWB As Excel.Workbook
Set ActiveWB = ActiveWorkbook
fileName = Application.GetOpenFilename("Comma Separated Values (*.csv),*.csv")
If fileName <> "False" Then
Workbooks.Open fileName, Format:=2
End If

' Imports the IPD Data
Application.DisplayAlerts = False

ActiveSheet.Cells.Copy
''fileName.Close
Workbooks("COF-IDPS-SAM-v.1.4.xlsm").Activate
Worksheets("RAW").Activate
ActiveSheet.Paste
Application.CutCopyMode = False 'Clear Clipboard
If Range("g1").Value = "F REQ UP" Then
Call Fix_Sheet
Else
Cells.Select
'' Selection.ClearContents
MsgBox ("IDP Export is not Staffing")

End If




MsgBox ("done")




End Sub

[hunsnowboard]

I don't know if it helps..try this:

With Workbooks("MyCSV.csv")
.Saved = True
.Close
End With

..and don't forget to set the Application.DisplayAlerts = True at the end of your code...

[HaHoBe]

Hi, mduff,

use an object for the CSV-File to be opened, and I prefer not to use the clipboard for copying:


Sub OpenFile()

Dim fileName
Dim ActiveWB As Excel.Workbook
Dim wbCSV As Workbook

Set ActiveWB = ActiveWorkbook
fileName = Application.GetOpenFilename("Comma Separated Values (*.csv),*.csv")

'Set object on Workbook to open for latter response
If fileName <> "False" Then
Set wbCSV = Workbooks.Open(fileName, Format:=2)
End If

''' Imports the IPD Data
''Application.DisplayAlerts = False
''ActiveSheet.Cells.Copy ''fileName.Close
''ActiveWB.Worksheets("RAW").Activate
''ActiveSheet.Paste
''Application.CutCopyMode = False

'/// another way to copy values instead of formulas
With wbCSV.ActiveSheet.UsedRange
ActiveWB.Worksheets("RAW").UsedRange.ClearContents
ActiveWB.Worksheets("RAW").Range(.Address).Value = .Value
End With

'Clear Clipboard
If Range("g1").Value = "F REQ UP" Then
Call Fix_Sheet
Else
Cells.Select
'' Selection.ClearContents
MsgBox ("IDP Export is not Staffing")
End If
wbCSV.Close savechanges:=False

'Application.DisplayAlerts = True
Set wbCSV = Nothing
Set ActiveWB = Nothing
MsgBox ("done")
End Sub

[Bob Phillips]

Use workbook variables



Dim wb As Workbook

If fileName <> "False" Then
Set wb = Workbooks.Open(fileName, Format:=2)
End If

'main code

wb.Close SaveChanges:=False
Set wb = Nothing

[mduff]

Thanks So Much