Excel 2010 Worksheet.copy with CSV file

[ADoozer]

Hi All.

I have a "simple" little question...

I use the following few lines of code all the time for importing a worksheet from an external excel workbook (xls, xlsm, csv) into my current excel workbook.

Public Sub LoadData()
    Dim xlWB As Excel.Workbook
    Dim xlMain As Excel.Worksheet
    
    'open the main sheet
    Set xlMain = ThisWorkbook.Worksheets("Main")
    
    'delete the allocation table if it exists
    DeleteSheet "FS"
    
    'load the failsafe allocation table
    Set xlWB = Workbooks.Open(xlMain.Cells(1, "B"), False, True, , , , , , , , , , False)
    xlWB.Sheets("FS").Copy After:=ThisWorkbook.Sheets(xlMain.Index)
    xlWB.Close (False)
    
    'housekeeping
    Set xlWB = Nothing
    Set xlMain = Nothing
End Sub

it works pretty well in older versions of excel, but I found out today that in Excel 2010 it does not work with *.csv files.

I keep getting runtime error 1004.

Am I missing something? or does excel 2010 not like to copy from a CSV worksheet into an xlsm workbook?

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[SkipVought]

Hi,

I make it a practice to NEVER open a .csv text file with Excel, as Excel can and will change your data under certain circumstances.

Rather, I IMPORT the text data using Data > Get external data... where each column can be explicitly specified for the type of data and conversion appropriate for that column.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I failed to add that once you understand the IMPORT process on you sheet, you can turn on your marco recorder to record the code. Post back for help customizing your recorded code. This process adds a querytable object to your sheet. Once you have a querytable object on your sheet, there is no needd to ADD another the next time you need to import another version of the same .csv.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

What is the additional error message?
"csv" is a text format, a file opened in excel has only one sheet having the name same as csv file. What is the contents of B1 in xlMain? In your case it should be directory + "FS.csv". In fact, in B1 you only need a directory (FS.csv can be in code), the rest is a source of potential error.
I agree with Skip, depending on regional settings and data, excel can interpret this file differently. It's better to use either text ("txt") format with import or regular excel file.

combo

 

[ADoozer]

SkipVought, combo.

The CSV files are generally exported files from PLC's and as such I haven't really had issues with formatting. (Duly noted your concerns with potential data changing).

This sub works in older versions of excel (2003, 2007) but for some reason does not work in 2010 with CSV files.

The error I get is:- "Run-time error 1004 Application defined or object defined error" at the line "xlWB.Sheets("FS").Copy After:=ThisWorkbook.Sheets(xlMain.Index)"

I tried changing ThisWorkbook for a reference to the original workbook thinking that it may be trying to copy the sheet into itself (where there is no sheet named xlMain) but same error.

Cell 1 B contains the path of the csv file including the csv file name "D:\some_path\FS.csv.

I don't want to do too much heavy work on this, as it works perfectly well for what I need (I can just run office 2003/2007 in a VM to get around it)

I guess that Excel 2010 just refuses to perform this "potential loss of data formatting" action.

Regards
Dave

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[combo]

Do you have instant access to the newly opened workbook? Does DoEvents make any change? I.e.:
1.
Set xlWB = Workbooks.Open(xlMain.Cells(1, "B"), False, True, , , , , , , , , , False)
Debug.Print xlWB.Cells(1,1)
2.
Set xlWB = Workbooks.Open(xlMain.Cells(1, "B"), False, True, , , , , , , , , , False)
DoEvents
xlWB.Sheets("FS").Copy After:=ThisWorkbook.Sheets(xlMain.Index)




combo

 

[SkipVought]

It seems to me that this is an iterative process, that you want the .csv contents in sheet FS.

Simply manually IMPORT your .csv ONE TIME, which will place a QueryTable on that sheet. Then each subsequent time, you need only REFRESH the QueryTable to replace the contenets with the data in the .csv.

This should be all the code you need to refresh the QT in the FS sheet...

    With Sheets("FS").QueryTables(1)
        .TextFilePromptOnRefresh = False
        .Refresh False
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ADoozer]

Combo.

Adding the debug line "Debug.Print xlWB.Sheets(1).Cells(1, 1)" prints to the immediate window without issue but the code still fails when copying the worksheet.

It really looks like 2010 is not capable of copying a csv sheet to a workbook!!

SkipVought

I will have a play around with the query table later.
I didn't really want to have to manually import things, (end user just wants to press a button on the main page and select the allocation table exports)

My work around at the moment is to open the csv file and save as a "standard" excel file.

Regards
Dave

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[SkipVought]

This is no manual solution!

The MANUAL part is 1) adding a querytable to import the .csv and 2) modifying your code.

The code AUTOMATICALLY imports from your .csv, just like your current code is intended to do, only without having code to delete a sheet, open the .csv Add a sheet.

It IS AUTOMATIC!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You can try Move instead of Copy. Additionally, omit second and third arguments of Open method (there are no links, workbook from "csv" file will be transferred to destination workbook).

combo