473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Import Export

759 512MB
Hi guys !
Thank you for help. My database work fin.
Now I like to put some "flowers" in it. Can you help me again ?

I need some code or some advices to export a table (or result of a query WITHOUT open it) to other programs (i e Excel) from VBA. I think is it possible, but how ?

How to import dates from Excel ? This dates must go in a table field and the primary key of this table (AUTO NUMBER) must be updated as well.

I think that I can write from VBA directly in an Excel worksheet (or reed from Excel) but I don't know how to establish a link to that worksheet.

Hope you understand my "english".

Thank you !
Apr 27 '11 #1

✓ answered by TheSmileyCoder

You use the DoCmd.TransferSpreadSheet method to import/export from/to excel. An example is shown below:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTblReport, strFileName, True
If you import into a table with a autonumber field, the autonumber field will automatically (oddly enough) fill in numbers for you.

To proceed with opening the file, and making modifications:
Expand|Select|Wrap|Line Numbers
  1. On Error Goto Err_Handler:
  2. Dim myexcel As New Excel.Application
  3.   myexcel.Workbooks.Open strFileName
  4. Dim mySheet As Worksheet
  5.   Set mySheet = myexcel.Worksheets(1)
  6.  
  7. Exit_Sub:
  8. 'Now either close excel, or make it visible.
  9.   myexcel.Visible = True
  10.   Set MyExcel=Nothing
  11.   Exit Sub
  12.  
  13. Err_Handler:
  14.   Msgbox Err.Nr & " - " & err.description
  15.   Goto Exit_Sub
  16. End Sub

2 2021
TheSmileyCoder
2,322 Expert Mod 2GB
You use the DoCmd.TransferSpreadSheet method to import/export from/to excel. An example is shown below:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTblReport, strFileName, True
If you import into a table with a autonumber field, the autonumber field will automatically (oddly enough) fill in numbers for you.

To proceed with opening the file, and making modifications:
Expand|Select|Wrap|Line Numbers
  1. On Error Goto Err_Handler:
  2. Dim myexcel As New Excel.Application
  3.   myexcel.Workbooks.Open strFileName
  4. Dim mySheet As Worksheet
  5.   Set mySheet = myexcel.Worksheets(1)
  6.  
  7. Exit_Sub:
  8. 'Now either close excel, or make it visible.
  9.   myexcel.Visible = True
  10.   Set MyExcel=Nothing
  11.   Exit Sub
  12.  
  13. Err_Handler:
  14.   Msgbox Err.Nr & " - " & err.description
  15.   Goto Exit_Sub
  16. End Sub
Apr 27 '11 #2
Mihail
759 512MB
Like usually TheSmileyCoder.
Your answer solve complete my question.

Thank you !
Apr 27 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Damien | last post by:
Hi to all, I need to design an import/export system. Data comes from a filemaker pro DB in a big CSV file. Some alterations are made on the data as it is imported into my mysql table. Data is...
5
by: steve | last post by:
Hi, When I copy tables in a database from one server to another using enterprise manager, everything copies ok, except for field defaults. Has anyone seen this, and what is the solution? --...
2
by: Alok | last post by:
Hi, I recently upsized my access database to an ADP project with SQL backend. However when I did it the import/export specs were not ported to the new database. Also in the ADP files I am unable...
3
by: Iavor Raytchev | last post by:
Hello, We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can be 5 000 can be 15 000). Each terminal presents unique data. The...
7
by: phillip.s.powell | last post by:
We're looking at a GUI interface for our MySQL DB and I am interested in MySQL Administrator, however, one of our requirements is to be able to import/export databases. Is this possible or do I...
1
by: maflatoun | last post by:
Hi, In SQL 2000 if I wanted to take a complete copy of another running sql database all did was create a new database locally and right-click it and select import and point to another database...
0
by: shubhangi | last post by:
Hi, My m/c has a client installation of sql 2005. Now I'm connecting to sql server 2005 through vpn. But here my import/export utility is disabled. Please can u tell me "is this a feature of...
12
by: joaotsetsemoita | last post by:
Hello everyone, im completly new to vb.net and I was assigned to do a simple piece of software that just had to select from um db in a MS access data base and insert into a SQL server Database....
4
by: Max2006 | last post by:
Hi, We are developing a SQL server based asp.net application. As part of requirement we should allow users import/export some relational data through web user interface. We are investigation...
3
by: t8ntboy | last post by:
I am migrating data from one database to another. The process requires that I significantly modify the table and data structure. Therefore, I am exporting the data to a csv, making the adjustments...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.