Copy diff workbooks into one workbook without opening the workbooks

N

navb

New Member
Joined
Mar 5, 2011
Messages
31
Could anyone please help me on this ? :
I have somewhere around 25 similar workbooks which I want to merge in a single sheet. I will only import 1 sheet (say sheet1) from each of those workbooks.I want to do this without opening any of the workbooks as this will save lot of time. Can this be done ? I have heard you can do this by using some ADO connection and then querying the excel database but dont know how to do this. Any help will be greatly appreciated !!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
This is possible to do without opening the workbooks. To do so you have to access the file via the, "OLEDB Engine". The problem is the connection string to open the file varies with different version of the OLEDB engine. As such the suggestions I offer below are only guidelines. See here for connection string examples: http://www.connectionstrings.com/

To test the code below I used a sample file with four fields (columns) of data.

The first thing I would do is populate an array with the filenames to process. I have assumed the files are in the same folder.

Code: 
   [COLOR=green]'==========================[/COLOR]
   [COLOR=green]'Get file names to process[/COLOR]
   [COLOR=green]'==========================[/COLOR]
   sPath = "[COLOR=red]c:\temp2\[/COLOR]"
   [COLOR=darkblue]If[/COLOR] Dir(sPath, vbDirectory) = "" [COLOR=darkblue]Then[/COLOR]
      MsgBox "Directory does not exist!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=green]'populate an array with filenames[/COLOR]
      sFile = sPath & Dir(sPath & "*.xls")
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] sFile = ""
         iFileCount = iFileCount + 1
         [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aFiles(1 [COLOR=darkblue]To[/COLOR] iFileCount)
         aFiles(iFileCount) = sFile
         sFile = Dir()
      [COLOR=darkblue]Loop[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

I would then loop through the array and process each file in turn.
Code: 
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aFiles) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aFiles)

Open the connection string, For my Excel 2003 it is:
Code: 
      [COLOR=green]'open the connection[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoConn = [COLOR=darkblue]New[/COLOR] ADODB.Connection
      adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & aFiles(i) & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes"";"

Copy all the data from the specified worksheet:
Code: 
      [COLOR=green]'open the recordset[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoRs = [COLOR=darkblue]New[/COLOR] ADODB.Recordset
      adoRs.Open "SELECT * FROM [[COLOR=red]Sheet1[/COLOR]$];", adoConn, adOpenStatic, adLockReadOnly

And then output the data into the current workbook.
NB there are four fields in the sample data
Code: 
      [COLOR=green]'process the data[/COLOR]
      [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] (adoRs.BOF [COLOR=darkblue]Or[/COLOR] adoRs.EOF) [COLOR=darkblue]Then[/COLOR]
        adoRs.MoveFirst
 
         [COLOR=green]'output to worksheet[/COLOR]
         [COLOR=darkblue]With[/COLOR] [COLOR=red]Sheets("Sheet1")[/COLOR]
            rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            [COLOR=darkblue]Do[/COLOR]
               [COLOR=green]'NB sample data has four fields[/COLOR]
               .Range("[COLOR=red]A[/COLOR]" & rw) = adoRs.Fields([COLOR=red]0[/COLOR])  [COLOR=green]'1st field[/COLOR]
               .Range("[COLOR=red]B[/COLOR]" & rw) = adoRs.Fields([COLOR=red]1[/COLOR])  [COLOR=green]'2nd field[/COLOR]
               .Range("[COLOR=red]C[/COLOR]" & rw) = adoRs.Fields([COLOR=red]2[/COLOR])  [COLOR=green]'3rd field[/COLOR]
               .Range("[COLOR=red]D[/COLOR]" & rw) = adoRs.Fields([COLOR=red]3[/COLOR])  [COLOR=green]'4th field[/COLOR]
 
               adoRs.MoveNext
               rw = rw + 1
            [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] adoRs.EOF
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

I would advise stepping through the code (F8) rather than running it.

The code will go into the ThisWorkbook module.
Set up a test file with four fields to test.
The full code is:
Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] adoConn [COLOR=darkblue]As[/COLOR] ADODB.Connection
   [COLOR=darkblue]Dim[/COLOR] adoRs [COLOR=darkblue]As[/COLOR] ADODB.Recordset
 
[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateMastersheet()
   [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] aFiles() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] iFileCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=green]'==========================[/COLOR]
   [COLOR=green]'Get file names to process[/COLOR]
   [COLOR=green]'==========================[/COLOR]
   sPath = "c:\temp2\"
   [COLOR=darkblue]If[/COLOR] Dir(sPath, vbDirectory) = "" [COLOR=darkblue]Then[/COLOR]
      MsgBox "Directory does not exist!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=green]'populate an array with filenames[/COLOR]
      sFile = sPath & Dir(sPath & "*.xls")
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] sFile = ""
         iFileCount = iFileCount + 1
         [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aFiles(1 [COLOR=darkblue]To[/COLOR] iFileCount)
         aFiles(iFileCount) = sFile
         sFile = Dir()
      [COLOR=darkblue]Loop[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=green]'==========================[/COLOR]
   [COLOR=green]'process the array of files[/COLOR]
   [COLOR=green]'==========================[/COLOR]
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errCloseConnection:
 
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aFiles) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aFiles)
      [COLOR=green]'open the connection[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoConn = [COLOR=darkblue]New[/COLOR] ADODB.Connection
      adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & aFiles(i) & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes"";"
      [COLOR=green]'open the recordset[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoRs = [COLOR=darkblue]New[/COLOR] ADODB.Recordset
      adoRs.Open "SELECT * FROM [Sheet1$];", adoConn, adOpenStatic, adLockReadOnly
 
      [COLOR=green]'process the data[/COLOR]
      [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] (adoRs.BOF [COLOR=darkblue]Or[/COLOR] adoRs.EOF) [COLOR=darkblue]Then[/COLOR]
        adoRs.MoveFirst
 
         [COLOR=green]'output to worksheet[/COLOR]
         [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
            rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            [COLOR=darkblue]Do[/COLOR]
               [COLOR=green]'NB sample data has four fields[/COLOR]
               .Range("A" & rw) = adoRs.Fields(0)  [COLOR=green]'1st field[/COLOR]
               .Range("B" & rw) = adoRs.Fields(1)  '2nd field
               .Range("C" & rw) = adoRs.Fields(2)  [COLOR=green]'3rd field[/COLOR]
               .Range("D" & rw) = adoRs.Fields(3)  '4th field
 
               adoRs.MoveNext
               rw = rw + 1
            [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] adoRs.EOF
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
[COLOR=green]' Tidy up[/COLOR]
exitCloseConnection:
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
   adoRs.Close
   adoConn.Close
   [COLOR=darkblue]Set[/COLOR] adoRs = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] adoConn = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
errCloseConnection:
   MsgBox "Description: " & Err.Description & vbCrLf _
         & "Error: " & Err.Number
   [COLOR=darkblue]Resume[/COLOR] exitCloseConnection
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps.
 
Upvote 0
the code is not able to take the 2nd workbook and onwards. it giving the following error on this line :
adoRs.Open "SELECT * FROM [Sheet1$];", adoConn, adOpenStatic, adLockReadOnly

Error :
the microsoft jet engine couldnt find the object "SHEET1$" . make sure the object exists and that you spell the name and path name correctly .....error 2147217865

I am getting this error even after creating a copy of 3 similar files...
 
Upvote 0
Ther error appears to be occuring on here.

The highlighted row selects everything (*) from the worksheet named "Sheet1".
If the worksheet to be imported has a name other than "Sheet1" then edit where highlighted.


Code: 
[COLOR=#008000]'open the recordset[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoRs = [COLOR=darkblue]New[/COLOR] ADODB.Recordset
      adoRs.Open "SELECT * FROM [[COLOR=red]Sheet1[/COLOR]$];", adoConn, adOpenStatic, adLockReadOnly
 
Upvote 0
I changed the following code :
sFile = sPath & Dir(sPath & "*.xls") to following:

sFile = Dir(sPath & "*.xls")

Its working fine now.. !!! I want to ask if there is a way to copy the entire sheet as 2D array instead of taking one record at a time. I am asking this coz if I have 65k records in a sheet then it will take lot of time for the loop to execute... I read somewhere that there is .getrows property....which gives 2D array....u need to transpose it before pasting....can u please help ?
 
Upvote 0
Yes, you were correct about assigning the full path for the files.
I have also closed the ADO connection to each file at the end of each iteration of the loop.
And I have amended the output to work as a data dump.

See the highlighted code for the changes I have made.

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] adoConn [COLOR=darkblue]As[/COLOR] ADODB.Connection
   [COLOR=darkblue]Dim[/COLOR] adoRs [COLOR=darkblue]As[/COLOR] ADODB.Recordset
 
[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateMastersheet()
   [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] aFiles() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] iFileCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   [COLOR=green]'==========================[/COLOR]
   [COLOR=green]'Get file names to process[/COLOR]
   [COLOR=green]'==========================[/COLOR]
   sPath = "c:\temp2\"
   [COLOR=darkblue]If[/COLOR] Dir(sPath, vbDirectory) = "" [COLOR=darkblue]Then[/COLOR]
      MsgBox "Directory does not exist!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=green]'populate an array with filenames[/COLOR]
      [COLOR=red]sFile = Dir(sPath & "*.xls")[/COLOR]
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] sFile = ""
        [COLOR=red] sFile = sPath & sFile[/COLOR]
         iFileCount = iFileCount + 1
         [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aFiles(1 [COLOR=darkblue]To[/COLOR] iFileCount)
         aFiles(iFileCount) = sFile
         sFile = Dir()
      [COLOR=darkblue]Loop[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=green]'==========================[/COLOR]
   [COLOR=green]'process the array of files[/COLOR]
   [COLOR=green]'==========================[/COLOR]
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errCloseConnection:
 
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aFiles) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aFiles)
      [COLOR=green]'open the connection[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoConn = [COLOR=darkblue]New[/COLOR] ADODB.Connection
      adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & aFiles(i) & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes"";"
      [COLOR=green]'open the recordset[/COLOR]
      [COLOR=darkblue]Set[/COLOR] adoRs = [COLOR=darkblue]New[/COLOR] ADODB.Recordset
      adoRs.Open "SELECT * FROM [Sheet1$];", adoConn, adOpenStatic, adLockReadOnly
 
      [COLOR=green]'process the data[/COLOR]
      [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] (adoRs.BOF [COLOR=darkblue]Or[/COLOR] adoRs.EOF) [COLOR=darkblue]Then[/COLOR]
        adoRs.MoveFirst
 
         [COLOR=green]'output to worksheet[/COLOR]
         [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
            rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            [COLOR=red].Range("A" & rw).CopyFromRecordset adoRs[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=red]    adoRs.Close[/COLOR]
[COLOR=red]    adoConn.Close[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
[COLOR=green]' Tidy up[/COLOR]
exitCloseConnection:
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
   adoRs.Close
   adoConn.Close
   [COLOR=darkblue]Set[/COLOR] adoRs = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] adoConn = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
errCloseConnection:
   MsgBox "Description: " & Err.Description & vbCrLf _
         & "Error: " & Err.Number
   [COLOR=darkblue]Resume[/COLOR] exitCloseConnection
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
There are at least 2 ways you can get work with multiple records.

If all you want to do is copy to a worksheet you can use CopyFromRecordset, if you want to return an array you can use GetRows.

Transposing the data is another matter, and you might actually find you need to use some sort of looping.

You might be able to do the transpose part in the query? That really depends on what and how you need to transpose.

By the way, have you considered opening the workbooks?

It might not take as long as you think it will, and it there could also be other ways to deal with the transpose if you do open them.
 
Upvote 0
I want to simply copy to worksheet.....the files are very large....so i want to avoid opening them
 
Upvote 0
Can I ask if you've tried opening them and doing what you want?

If all you want to do is get the data from the files there are various things you could try that might speed up their opening.

eg turning off automatic calculation/screenupdating/events etc

Using ADO will work but it might not be that quick.:)
 
Upvote 0
Hi Bertie

I have modified the code to include .xls, .xlsm, .xlsb and .xlsx file the code looks like

Option Explicit
Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
'This code merges the contents of all the worksheets from different workbooks. The assumption here is that the contents are in _
sheet 1 of all the workbooks and we import only one sheet from each workbook
Public Sub PopulateMastersheet()
Dim sPath As String
Dim sFile As String
Dim aFiles() As Variant
Dim iFileCount As Integer
Dim i As Integer
Dim rw As Long
Dim arrFiles As Variant
Dim file_ext As String
Dim objFSO, objFile
Dim FileExists As Boolean
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

'==========================
'Get file names to process
'==========================
aFiles = Application.GetOpenFilename("", , , , True)

'Mid(List_America.List(0), InStrRev(List_America.List(0), "\") + 1, _
Len(List_America.List(0)))
'sPath = "C:\Documents and Settings\nbihani1\Desktop\bhavani\"
sPath = Mid(aFiles(1), 1, InStrRev(aFiles(1), "\"))
If Dir(sPath, vbDirectory) = "" Then
MsgBox "Directory does not exist!"
Exit Sub
Else
'populate an array with filenames
sFile = Dir(sPath & "")
Do Until sFile = ""
sFile = sPath & sFile
iFileCount = iFileCount + 1
ReDim Preserve aFiles(1 To iFileCount)
aFiles(iFileCount) = sFile
sFile = Dir()
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Loop</st1:place>
End If
'==========================
'process the array of files
'==========================
With ThisWorkbook
.Sheets.Add After:=.Sheets(.Sheets.Count), _
Type:="worksheet", _
Count:=1

End With

On Error GoTo errCloseConnection:

For i = LBound(aFiles) To UBound(aFiles)
Set objFSO = CreateObject("Scripting.FileSystemObject")
FileExists = objFSO.FileExists(aFiles(i))
Select Case FileExists
Case True: file_ext = objFSO.GetExtensionName(aFiles(i))
Case False: GoTo exitCloseConnection
End Select
Set objFSO = Nothing: Set objFile = Nothing
Set adoConn = New ADODB.Connection

'MsgBox aFiles(i)
'If file_ext = "xlsx" Then
Select Case file_ext
Case "xlsx"
adoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & aFiles(i) & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=No"";"

Case "xlsm"
adoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & aFiles(i) & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=No"";"
'Else
'open the connection
Case "xls"
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & aFiles(i) & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"

Case "xlsb"
adoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & aFiles(i) & ";" & _
"Extended Properties=""Excel 12.0 ;HDR=No"";"

Case "csv"
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & aFiles(i) & ";" & _
"Extended Properties=""text ;HDR=No"";FMT=Delimited"

End Select
'End If
'open the recordset
Set adoRs = New ADODB.Recordset
adoRs.Open "SELECT * FROM [Sheet1$];", adoConn, adOpenStatic, adLockReadOnly

'process the data
If Not (adoRs.BOF Or adoRs.EOF) Then
adoRs.MoveFirst

'output to worksheet
With Sheets(ThisWorkbook.Sheets.Count)
rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & rw).CopyFromRecordset adoRs
End With
End If
adoRs.Close
adoConn.Close

Next i

' Tidy up
exitCloseConnection:
On Error Resume Next
adoRs.Close
adoConn.Close
Set adoRs = Nothing
Set adoConn = Nothing
Exit Sub
errCloseConnection:
MsgBox "Description: " & Err.Description & vbCrLf _
& "Error: " & Err.Number
Resume exitCloseConnection
End Sub
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>

I want to implement .csv file in it also. The problem with .csv files is that they have only one sheet and the name of the sheet is same as that of csv file(after removing the extension). How can we change the code to implement this ?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Transferring Data Between Two Open Workbooks
Replies
6
Views
155
NorthbyNorthwest
N
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
251
cooleomter
C
S
  • Question Question
Is there an alternate way to look into an excel sheet instead of connections and queries?
Replies
6
Views
132
SPS41
S
S
  • Solved
How do I automate opening an excel document every 24 hours in the background, refresh connections, save and close the workbook in excel using vba?
Replies
6
Views
229
SPS41
S
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
151
howard
H

Forum statistics

Threads
1,224,521
Messages
6,179,278
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top