Trying to copy a worksheet to an existing workbook in vba

I'm trying to copy data from a worksheet, opened from a CSV file, as a new worksheet in an existing Excel template. I have tried copying to an existing, empty worksheet as well as copying the source worksheet in as a new sheet in the target workbook. All of these methods have thrown a variety of errors. The only method that actually allowed the code to complete was a copy-paste-special command. However, it resulted in cells being filled with binary as opposed to values and many cells were filled with a grayed out appearance.

Below is the code I have been trying to get working:

'=================================================
'Add Data
'=================================================
Dim AppExcell As Object
Dim wb As Object
Dim xFile As String
Dim main As Workbook

Set AppExcel = CreateObject("Excel.Application")
AppExcel.Visible = False

Set wb = AppExcel.Workbooks.Add("C:Fridge_AutomationLab Report.xltm")
Set main = ActiveWorkbook

xFile = Application.GetOpenFilename("All CSV Files (*.csv),*.csv", , "Select CSV File")

Set src = Workbooks.Open(xFile)

src.Worksheets(1).Copy Before:=wb.Worksheets("11Mic Avg - Raw Data")
wb.Worksheets(2).Name = "Raw Data"
src.Close

I am running this code in Excel 2013 by clicking a button I have added to a worksheet.

---

The below code worked for me, run from within a workbook. *** flags things I changed.

Option Explicit             ' *** Always use this in every module
Option Base 0

Public Sub GrabSheet()

    'Dim AppExcel As Object ' *** don't need this
    'Dim wb As Object       ' ***
    Dim dest As Workbook    ' *** Instead of "wb"
    Dim xFile As String
    'Dim main As Workbook   ' ***

    'Set AppExcel = CreateObject("Excel.Application")   ' ***
    'AppExcel.Visible = False   ' ***
    'Application.Visible = False ' *** Uncomment if you really want to...

    Set dest = ActiveWorkbook   ' *** for testing - use Workbooks.Add("C:Fridge_AutomationLab Report.xltm") for your real code
    'Set main = ActiveWorkbook  ' *** don't need this

    xFile = Application.GetOpenFilename("All CSV Files (*.csv),*.csv", , "Select CSV File")

    Dim src As Workbook     ' *** Need to declare this because of "Option Explicit"
    Set src = Workbooks.Open(xFile)

    ' Per https://stackoverflow.com/q/7692274/2877364 , it is surprisingly
    ' difficult to get the new sheet after you copy.
    ' Make a unique name to refer to the sheet by.
    Dim sheetname As String                         ' ***
    sheetname = "S" & Format(Now, "yyyymmddhhmmss") ' ***
    src.Worksheets(1).Name = sheetname              ' ***

    src.Worksheets(1).Copy Before:=dest.Worksheets("11Mic Avg - Raw Data")  ' *** changed wb to dest
    'dest.Worksheets(2).Name = "Raw Data"           ' *** don't assume an index...
    dest.Worksheets(sheetname).Name = "Raw Data"    ' *** ... but use the name.
        ' *** NOTE: this fails if a "Raw Data" sheet already exists.
    src.Close SaveChanges:=False  ' *** Suppress the "save changes" prompt you otherwise get because of the `src...Name` assignment

End Sub

I used a custom sheet name to find the new sheet because of the issues listed in this question.

You don't need to create an AppExcel object when you are running from within Excel. Instead, you can just refer directly to Application .

链接地址: http://www.djcxy.com/p/35772.html

上一篇: Web应用程序中的免费SMS API

下一篇: 尝试将工作表复制到vba中的现有工作簿