CODEWITHSUNDEEP
vbaexcelcsv
user8239586
user8239586

Reputation:

Copy and paste to a closed workbook

My question is it possible to copy and paste to a closed workbook and then save that as a CSV? The first workbook is active though I suppose copying (from a closed workbook) and pasting (to a closed workbook) would be ideal but not essential.

I'm sorry if this is a silly question I am just wondering as to save my CPU any unnecessary hardship and having to open so many excels.

The code below tends to work well enough though I am stumped as to how you would achieve pasting to a specific CSV then saving it as generally they are required to be active.

That being said you can copy from a closed workbook to an Active one though I require the opposite.

Any help is really appreciated :).

Option Explicit

    Sub copytoarchive()
    Dim wb1 As Excel.Workbook
    Set wb1 = Workbooks.Open("C:\Users\Excel.xlsx")
    Dim wb2 As Excel.Workbook
    Set wb2 = Workbooks.Open("C:\Users\CSV.csv")
    wb1.Sheets("Sheet1").Range("A1:Z10000").Copy
    wb2.Sheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    wb1.Close SaveChanges:=True
    End Sub

Upvotes: 0

Views: 1793

Answers (3)

ASH
ASH

Reputation: 20302

AFAIK, yo uhave to open the file. Maybe this will do what you want...

Sub ImportDatafromcloseworkbook()
'Update 20150707
Dim xWb As Workbook
Dim xAddWb As Workbook
Dim xRng1 As Range
Dim xRng2 As Range
Set xWb = Application.ActiveWorkbook
xTitleId = "KutoolsforExcel"
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Application.Workbooks.Open .SelectedItems(1)
        Set xAddWb = Application.ActiveWorkbook
        Set xRng1 = Application.InputBox(prompt:="Select source range", Title:=xTitleId, Default:="A1", Type:=8)
        xWb.Activate
        Set xRng2 = Application.InputBox(prompt:="Select destination cell", Title:=xTitleId, Default:="A1", Type:=8)
        xRng1.Copy xRng2
        xRng2.CurrentRegion.EntireColumn.AutoFit
        xAddWb.Close False
    End If
End With
End Sub

End Sub

Upvotes: 0

ASH
ASH

Reputation: 20302

Maybe this.

Sub CopynPasteWrkBk()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String'

' Set path for Input & Output 
fileInputpath = "D:\"
Outputpath = "D:\Output\"

'## Open both workbooks first:
Set InputFile = activeworkbook
Set OutputFile = Workbooks.Open(Outputpath & "Time.xlsx")

'Now, copy what you want from InputFile:
InputFile.Sheets("Sheet2").Activate
InputFile.Sheets("Sheet2").Range("B1:K100").Copy

'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A1").PasteSpecialOutputFile.Save

'Close InputFile & OutputFile:
InputFile.Close
OutputFile.Close

End Sub

Upvotes: 0

ainwood
ainwood

Reputation: 1048

You do need to open a file in order to write to it. However, in this specific case, because a .csv file is a delimited text file, you can write to it without opening it in Excel. I don't know if it is actually any faster, but you can try it. Something like this:

Option Explicit

Sub copytoarchive()
Dim wb1 As Excel.Workbook
Set wb1 = Workbooks.Open("C:\Users\Excel.xlsx")

'//Set variables to help with delimiting the workbook contents
Dim row As Range
Dim col As Long
Dim sRowContents As String

'//Open the file for Append
Open "C:\Users\CSV.csv" For Append As #1

'//Work through each row, and create a comma delimited set of the contents
For Each row In wb1.Sheets("Sheet1").Range("A1:Z10000").Rows
    For col = 1 To row.Columns.Count
        sRowContents = sRowContents & row.Cells(1, col).Value & ","
    Next col
    Print #1, sRowContents  '//<= This is where the delimted line gets added.
    sRowContents = ""    '//Clear the value for the next row.
Next row
Close #1

wb1.Close SaveChanges:=True
End Sub

Upvotes: 1

Related Questions