copy, import, export data between Excel and Access

Exchange (copy, import, export) data between Excel and Access

There are several ways to exchange data between Microsoft Access and Microsoft Excel.

• To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.
• To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

 NOTES 

• The word "import" has two different meanings between Excel and Access. In Excel, when you import, you make a permanent connection to data that can be refreshed. In Access, when you import, you bring data into Access once, but without a permanent data connection.
• You cannot save an Excel workbook as an Access database. Neither Excel nor Access provides functionality to create an Access database from Excel data.

What do you want to do?

• Work with Access data in Excel
• Work with Excel data in Access

Work with Access data in Excel

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or the many functions that are not available in Access.

Copy Access data into Excel

From Access, you can copy data from a datasheet view and then paste the data into an Excel worksheet.

1. Start Access, and then open the table, query, or form that contains the records that you want to copy to Excel.
2. On the Home tab, click View, and then click Datasheet View.
3. Select the records that you want to copy.

If you want to select specific columns, drag across adjacent column headings.

4. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

5. Start Excel, and then open the worksheet that you want to paste the data into.
6. Click in the upper-left corner of the worksheet area where you want the first field name to appear.

To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell that you click.

7. On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut  You can also press CTRL+V.

 TOP OF PAGE

Export Access data to Excel

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, or form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details of the operation for future use, and even schedule the export operation to run automatically at specified intervals.

The following are common scenarios for exporting data from Access to Excel:

• Your department or workgroup uses both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyze the data and to distribute the results of your analysis. Your team currently exports data to Excel when they need to, but you would like to make this process more efficient.
• You are a long-time user of Access, but your manager prefers to view reports in Excel. At regular intervals, you do the work of copying the data into Excel, but you would like to automate this process to save yourself time.

For more information about exporting data from Access to Excel, see the Access Help system.

 TOP OF PAGE

Connect to Access data from Excel

To bring Access data that can be refreshed into Excel, you can create a connection, often stored in an Office Data Connection file (.odc), to the Access database and then retrieve all of the data from a table or query. The main benefit of connecting to Access data instead of importing it is that you can periodically analyze this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically refresh (or update) your Excel workbooks from the original Access database whenever the database is updated with new information. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

1. Click the cell where you want to put the data from the Access database.
2. On the Data tab, in the Get External Data group, click From Access.

3. Locate and double-click the Access database that you want to import.

In the Select Table dialog box, click the table or query that you want to import, and then click OK.

4. In the Import Data dialog box, do the following:
• Under Select how you want to view this data in your workbook, do one of the following:
• To view the data as a table, select Table.
• To view the data as a PivotTable report , select PivotTable report.
• To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report.
• Optionally, click Properties to set refresh, formatting, and layout options for the imported data, and then click OK.
• Under Where do you want to put the data? do one of the following:
• To return the data to the location that you selected, click Existing worksheet.
• To return the data to the upper-left corner of the new worksheet, click New worksheet.
5. Click OK.

Excel puts the external data range in the location that you specify.

For more information about connecting to data see, Connect to (Import) external data.

Work with Excel data in Access

You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users might find particularly useful for their Excel data:

• Reports    If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports.
• Forms    If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or view a lengthy row of data more easily on one screen.

For more information about working with Access forms and reports, see the Access Help system.

Copy Excel data into Access

From Excel, you can copy data in a worksheet view and then paste the data into an Access datasheet.

 NOTE   If you paste data from multiple fields in a worksheet to a datasheet, make sure that the columns match the order of the data that you want to copy. When you paste data from multiple fields to a form, Access pastes the data into fields with the same name as the source fields, regardless of their order on the form. If the data that you want to copy contains fields that don't exist in the destination form, Access asks you if you want to paste only fields with matching names. If there are no matching field names, Access pastes the fields according to the destination form's tab order, and this might not be the order that you want. If the field names in the source are different from the field names in the destination, you might want to paste the data into a datasheet instead of a form.

1. Start Excel, and then open the worksheet that contains the data that you want to copy.
2. Select the rows that you want to copy.
3. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

4. Start Access, and then open the table, query, or form in which you want to paste the rows.
5. On the Datasheet tab, in the Views group, click View, and then click Datasheet View.

6. Do one of the following:
• To replace records, select those records, and then on the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut  You can also press CTRL+V.

• To append the data as new records, on the Home tab, in the Clipboard group, click Paste Append on theEdit menu.

 TOP OF PAGE

Import Excel data into Access

To store data from Excel in an Access database and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

The following are common scenarios for importing Excel data into Access:

• You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases.
• Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them.
• You use Access to manage your data, but the weekly reports that you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.

For more information about importing data from Excel to Access, see the Access Help system.

Link to Excel data from Access

You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want it to be available from within Access. You create this type of link from within the Access database, not from Excel.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source database file.

The following are common scenarios for linking to an Excel worksheet from within Access:

• You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access.
• Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don't want to maintain copies of external data, but want to be able to work with it in Access.

For more information about linking data from Access to Excel, see the Access Help system.

Lingking Lembar Kerja Ms.Excel dengan Ms.Access

Lingking Lembar Kerja Ms.Excel dengan Ms.Access

 

Bekerja dengan software MS.Office mayoritas orang sudah tau dasar dalam menjalankan program tersebut, karena program tersebut merupakan program yang sering kita gunakan untuk menyelesaikan laporan atau tugas makalah kita serta dapat pulah kita gunakan dalam hal pengolahan data. Akan tetapi, mungkin saja ada hal-hal yang tidak kita ketahui dari fitur program tersebut. Hal atau cara yang di maksud adalah bagaimana mengkoneksikan atau menghubungkan file yang di input di excel muncul di jendela lembar kerja Ms.Access.

Adapun fungsi dari penghubungan tersebut agar data yang kita simpan di file Excel dapat di munculkan di Ms.Access agar data yang kita input selalu ter update dan bisa kita gunakan sebagai file cadangan apabila salah satu dari file tersebut mengalami corupt.

Langkah-langkah menghubungkan Ms.Excel dengan Ms.Access

1. Buka lembar kerja Ms.Excel.
2. Input seluruh data-datanya dan simpan sesuai dengan nama file yang anda inginkan.
3. Buka lembar kerja Ms.Access
4. Seteleha anda buka, pada tab External Data di group Inport silahkan klik tombol Excel.
5. Langkah selanjutnya, klik pada option button Link to the data source by creating a linked table dan cari nama file Excel yang anda simpan dengan mengklik tombol Browse.
6. Setelah file sudah di dapat klik tombol OK.
7. Jika muncul kotak dialog Link Spreadsheet Wizard anda klik saja Next dan Next sampai muncul Linked Table Name, lalu isikan nama tabelnya pada kotak teks tersebut lalu klik tombol Finish.
8. Jika anda ingin melihat hasilnya tinggal doubleklik nama tabelnya,dan selesai.

Sekian apa yang bisa saya share sama teman atau sahabat yang baik, semoga artikel ini bermanfaat serta dapat mengatasi permasalahan anda.

Agenda Menjelang UAS

1

^Calendar Ms. Outlook 2010^

Posted on September 29, 2011 by itdoubled

Image

mari kita mulai membahas Calendar pada Ms. Outlook dan fungsi-fungsi yang saya ketahui dari Kuliah Komputer 3 Minggu lalu, seperti biasa kita ikuti langkah-langkahnya :

• Buka Ms. outlook 2010

• Klik –> Calendar, seperti tampilan gambar  di bawah ini

• pada calendar kita bisa melihat tampilan dengan hari, mingguan, dan bulan. eiits…. sebelum memulai mengedit sebuah appointment atau jadwal kita , setting dulu calendar option kita ya .. sebenarnya calendar option lebih sesuai dengan setting kita sendiri dan tidak mengikuti setting yang diberikan outlook untuk kita atau disebut default setting. caranya: 

kita tinggal memberi centang hari apa saja yang kita inginkan untuk dicantumkan pada calendar outlook

selain dapat mengisi hari apa saja , isi start time dan end time sesuai waktu kita beraktifitas.

2) Calendar Option, saya jelaskan singkat saja:

^ show click to add prompts on  the calendar: untuk menampilkan pemberitahuan jadwal dan perjanjian pada calendar

^ show week number in the month view and date navigator : untuk memberitahu tampilan minggu ke berapa

^ allow attendees to propose new times for meetings your organizer : mengijinkan pemberitahuan dari orang lain.

* pada use this response when you propose new meeting times silahkan memilih :

- Tentative : jika anda bisa antara menerima appointment itu, atau

- accept : menerima terus tiap ada appointment

- Decline : Menolak terus.

penjelasan diatas dapat dilihat pada  gambar berikut ini :

Planner Option and Add holiday

pengaturan untuk menunujukkan jadwal meeting anda saat bekerja dan settingan default sesuai atau tidak hanya beri tanda centang saja.

dan add Holiday :memilih negara yang sesuai dengan hari libur sesuai Negara yang kita tinggali atau mungkin kita buth negara lain hanya beri tanda Centang.

3)Advanced Option:

hanya jika kita ingin share melalui Internet  iCalendar pada Outlook.

- free/busy option : untuk memilih lokasi  mana yang akan kita share melalui internet, untuk permintaan jadwal meeting.

- Resource scheduling : untuk secara otomatis menerima permintaan meeting dan proses pembatalan sekaligus. beri tanda centang saja jika dibutuhkan.

selanjutnya terdapat Enable alternate calendar jika kita mengijinkan mengshare calendar pada Ms. outlook kita, dan When sending meeting requests over the internet, use icalendar format untuk mengirim permintaan meeting dengan format internet calendar, beri tanda centang jika perlu. jika memberi tanda centang setting default untuk bahasa akan otomatis menggunakan english.

klik –> Ok untuk menyimpan setting yang telah dibuat ^_^

Cara Membuat Appointment

1. kita aktifkan Calendar dan pilih pada tanggal berapa kita akan membuat perjanjian dan berbagai jadwal lainnya. klik ganda pada tanggal yang diinginkan untuk mempermudah. lalu akan muncul gambar seperti berikut:

Keterangan :

subject      : isikan dengan perintah apa, jadwal apa, dan perjanjian apa yang akan kita buat pada tanggal tersebut.

Location   : dimana sebuah perjanjian akan dilaksanakan

start time : pukul berapa acara dimulai, seperti ; meeting, jadwal kuliah dll

end time   : waktu berakhirnya acara atau perjanjian yang dibuat.

kita juga bisa menambahkan setting sesuai dengan yang kita butuhkan , pada gambar dibawah ini Show as : berarti kita bisa memilih bahwa pada perjanjian tersebut kita busy atau sibuk, tentative tidak jelas, Free atau bisa , out of office atau tidak ada di ruangan.

Reminder : untuk memberi alarm atau mengingatkan kita jika ada perjanjian yang telah dibuat pada tanggal tersebut bisa 15 minute dan akan muncul 15 menit sebelum perjanjian dimulai.

fungsi Reccurence: untuk mengatur sampai kapan jadwal meeting, pertemuan, jadwal kuliah dll. akan terus berada pada calendar outlook kita, setting reccurences akan membantu kita menyesuaikan dengan apa yang kita mau.

jika sudah mengklik –> Recurrence, akan muncul seperti gambar di bawah ini , berikut penjelasannya:

Appointment time;

start: kapan dimulai

end: kapan berakhirnya

duration: jangka waktu pilih dropdown jika ingin merubah sesuai yang diinginkan.

Reccurence Pattern

aturan pada jadwal yang akan diulang atau tidak pada tiap hari, tiap minggu tiap bulan dan tahun.  dan jika memilih daily atau tiap hari kita bisa memberi tanda centang untuk jadwal akan berulang tiap hari yang kita inginkan.

Range of reccurence

start                : tanggal, hari dan tahun kita memulai membuat sebuah appointment

No end date : jika kita terus ingin ada perjanjian yang kita buat terus ada pada Calendar outlook kita

end after       : isi sampai berapa kali perjanjian akan dimunculkan, misalnya isi dengan 10 , maka perjanjian akan muncul hingga 10 hari pada calendar.

end by           : akan otomatis mengikuti end after , berisi tanggal berakhirnya appointment kita.

Categorize : kita bisa memudahkan untuk mengingat appointment yang telah dibuat dengan memberi warna ;)

jika semua pengaturan sudah sesuai klik save and close;

berikutnya setelah di save appointment tadi akan terlihat pada calendar, pilih High jika tidak appointment, seperti gambar yang saya lingkari di bawah ini jika tidak dipilih high maka tidak bisa terlihat saya mengaktifkan tampilan bulan atau month jadi akan terlihat seperti ini:

appointment yang telah dibuat tadi akan mengingatkan kita dan akan muncul pada Ms. outlook kita dengan tampilan seperti dibawah ini:

Dismiss All: untuk menghapus semua alarm yang telah dibuat

Dismiss : jika Alarm sudah tidak diperlukan

Send Calendar Via-Email

pada  dropdown Calendar :

1) calendar : hanya menampilkan pada teman kita ketersediaan waktu kita, sibuk atau tidaknya yang akan dikirim.

2) Calendar in Archieve folder : untuk memberikan semua rincian yang ada pada calendar.

pada date range : jadwal yang kapan yang akan dikirim, jika ingin menunjukkan waktu kerja atau jam kerja yang telah terjadwal pada ooutlook beri tanda centang Show time within my working hours only. lalu –>OK

setekah klik –> Ok Calendar akan siap dikirim melalui mail messages,

^cara Mengimpor Kontak dari Outlook Ke Gmail^

1. pada Toolbar file pilih Import dan Export

2. selanjutnya pilihExport to a file karena kita akan mengirim Calendar pada Gmail

3. klik –> Next dan pilih Comma Separated Values (DOS)

4. klik –>Next dan Pilih Contact

5. klik –> Next dan Pilih Browse, untuk menyimpan hasil Calendar kita tinggal memilih dimana kita akan menyimpan hasil export Calendar tadi.

6. setelah browse untuk menyimpan file , seperti gambar di bawah ini klik –>next

7. selesai , klik –> Finish

Hasil export contact pada outlook  yang sudah disimpan tadi akan kita masukkan ke Gmail, ikuti langkah-langkahnya

• Log in pada Gmail
• pilih–> Kenalan

• pilih –> Impor data Kenalan

• pilih Browse , untuk memilih  folder yang digunakan untuk menyimpan File Contact outlook kita tadi.

• klik –> Impor

• akan muncul nama-nama teman pada contact outlook seperti yang sudah diimpor tadi:

=====hanya itu yang bisa saya bagi, semoga bermanfaat buat kalian yang mau mengenal outlook secara singkat ..hohohoho *_*====