Importing and Exporting data from workbook to workbook in excel

Hi all,

I'm after a code that will allow me to export data in one worksheet to a worksheet on a central server, and then a separate macro in another workbook that will allow me to import the data that is now housed on the central server.

The data being copied it the entire sheet, so from A1 to V196.

Any help would be greatly appreciated.

Thanks,

Daniel.

Thanks for the replies

arlu1201 - I would like the macro to do it, the server is close by so that wont be an issue, I wont be re uploading once the data has been copied. I plan to use the central server workbook just to house the data and which the first workbook will upload to and that the second book will import from. The data will be overwritten in the central server workbook whenever it is uploaded, with the new uploaded data from the first workbook.

snb - Thanks, I will check that out

Hi D
.
Below is some code and an attached WB that uses ADO to get data from a Source WB and send
it to a Target WB
.
AssumptionsCode can be modified to adjust these)
.
This WB (Manager.xlsm) will:
Get data from a WB name Source.xlsx
Send the data to a WB named Target.xlsx

Code:
Alt+F11
CTR+R
Double Click on Module1
ENTER Subroutine:
Public Sub Main_From_SourceToTarget()

Required:
A WB name SOURCE.XLSX in the SAME directory as this WB
A WB name TARGET.XLSX in the SAME directory as this WB

Code Assumptions:
Assumes that ALL THE WB's are Excel 2007
Assumes that the Source WB has data on a WS named Sheet1

Assumes TOOLS -> REFERENCES
MicroSoft ActiveX Data Objects 2.8 Library
MicroSoft ActiveX Data Objects Recordset 2.8 Library
MicroSoft ADO Ext 2.8 for DDL and Security

Please Login or Register  to view this content.

Attached is the WB named Manager. This code has been tested.

hth
regards
John

Hi John,

Thank you very much for your detailed response, I very much appreciate it.

I'm having a slight problem, i keep getting a "52 Bad file or number error"

Public Sub Main_From_SourceToTarget()

'http://www.excelforum.com/excel-programming/808336-importing-and-exporting-data-from-workbook-to-workbook-in-excel.html

On Error GoTo EH_Main_From_SourceToTarget

Dim lng As Long
Dim i As Integer

'-------------------
'-------------------
ms_SubName = "Public Sub Main_From_SourceToTarget()"

'-------------------
' This WB
'-------------------
Set mo_WBThis = ThisWorkbook
ms_WBThisVersion = Application.Version
ms_WBThisPath = ThisWorkbook.Path

'-------------------
' WB Source
'-------------------
ms_WBSourceName = "Source.xlsx" '<------- Change to real after testing this
ms_WBSourcePath = ms_WBThisPath & "C:\Documents and Settings\frances\Desktop\New Folder\Source.xlsx" '<------- Change to real after testing this
ms_WBSourceFullName = ms_WBSourcePath & ms_WBSourceName

'-------------------
' WB Target
'-------------------
ms_WBTargetName = "Target.xlsx" '<------- Change to real after testing this
ms_WBTargetPath = ms_WBThisPath & "C:\Documents and Settings\frances\Desktop\New Folder\Target.xlsx" '<------- Change to real after testing this
ms_WBTargetFullName = ms_WBTargetPath & ms_WBTargetName

'-------------------
' Check WB Source EXISTS
'-------------------
mb_Bool = IsFile(ms_WBSourceFullName)
If Not mb_Bool Then
MsgBox "File " & ms_WBSourceFullName & vbCrLf & vbCrLf & "Does not exist." & vbCrLf & vbCrLf & "Terminating ................ ", vbCritical, ms_SubName
Exit Sub
Else
'-------------------
' IGNORE
' FYI - Not Necessary but works
'-------------------
If 1 = 2 Then
Set mo_WBSource = Application.Workbooks.Open(ms_WBSourceFullName)
End If
End If

'-------------------
' Check WB Target EXISTS
'-------------------
mb_Bool = IsFile(ms_WBTargetFullName)
If Not mb_Bool Then
MsgBox "File " & ms_WBTargetFullName & vbCrLf & vbCrLf & "Does not exist." & vbCrLf & vbCrLf & "Terminating ................ ", vbCritical, ms_SubName
Exit Sub
Else
'-------------------
' FYI - Not Necessary but works
'-------------------
If 1 = 2 Then
' IGNORE
Set mo_WBTarget = Application.Workbooks.Open(ms_WBTargetFullName)
End If

I have changed the file names to Source and Target, and source has a sheet named Sheet1, are in the same directory as the file you gave me and I cannot work out for the life of me why this error is coming up!

Also, just to add, I think i will need the target work book to have a macro to import the data as opposed to being exported to as the target file name will change.

I think it will need to work like so : Source ---->export to Central workbook------>Target Imports from central workbook.

Sorry for being so demanding, I'm not even sure it this is possible!

Daniel

Hi Dans

Perspective:

What we want to do first is PROVE the feasibility of the solution I gave you. It is _always_ possible
that I gave you a _bad_ solution. Once we PROVE the feasibility of what you have - then - it is (I think)
just a matter of changing the file names. And that means we can SWING both ways - so don't worry
about getting the data FROM:Server File TO:Local File. but _FIRST_ we have to PROVE feasibility.

(1) Are you using Excel 2007 -OR- Excel 2003?

If you are using Excel 2003 - my code is __BAD__ and I have to change it. Its a No brainer.

(2) Have you and are you comfortable with putting your cursor inside the routine and F8 thru
the code. This means you are walking down the code and we can SEE which line the code
BLOWS UP?

(3) For right now - we are ONLY interested in creating two DUMMY Excel files.

One file is called SOURCE.XLSX and one file is called TARGET.XLSX. I did not post this
but you would put some dummy data on SOURCE.XLSX on sheet 1.

(4) If you meet all the conditions above (Excel 2007) then go into the routine and F8 thru
the code and please tell me WHAT LINE of code blows up. By this I mean - an error will occur
on the line - and the code will immediately goto the ERROR routine at the very end of the
sub routine.

If I am confusing you please YELL at me and we will try again. This stuff is _not_ hard and
we will get there.

regards
John

Hi ON

You are CORRECT.

As written - the code does NOT required all 3 references.

These 2 are NOT required. (As you have pointed out)
MicroSoft ActiveX Data Objects Recordset 2.8 Library
MicroSoft ADO Ext 2.8 for DDL and Security

My reason for putting them there would sound like a cop-out so best to say they are NOT needed.

regards
John

Thanks again for the replies,

Arlu, your code worked for me, is there anyway I can automate the file paths in the code, is there away to make it so that i wont have to choose where the file is located? The file being exported to and imported from does not change location or name.

John, I will go through it later and get back to you.

Thanks,

Daniel

Hi dan

Arlu's code looks good and is easier to manage. I would hang with it.

No need to get back to me.

regards
John