Solved

exporting Access table to excel + DoCmd object.....urgent, please help!

Posted on 2004-09-14
21
434 Views
Last Modified: 2008-02-01
Hi, I am trying to use DoCmd object to export data from Access database to excel. I found this sample but when I applied to my code (vb.net win app.) It said  DoCmd is not declared.  

DoCmd.OutputTo(acQuery, "qry_month", "MicrosoftExcel(*.xls)", "D:\Recertifications.xls")

Is it right to use it and how to declare the object? It is urgent. Please help

thanks,

k
0
Comment
Question by:kate_y
  • 11
  • 10
21 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 12058390
I found this sample for using DoCmd........

Sub PrintReport(strCategoryName As String)
   Dim acApp         As New Access.Application
   Dim strDBPath     As String

   Const DB_PATH As String = _
      "c:\program files\microsoft office\office\samples\northwind.mdb"

   acApp = New Access.Application
   With acApp
      .OpenCurrentDatabase DB_PATH
      ' Print the Product Catalog report.
      .DoCmd.OpenReport "Catalog", acViewNormal, , _
         "CategoryName = '" & strCategoryName & "'"
   End With
   acApp.Quit
   Set acApp = Nothing
End Sub
0
 

Author Comment

by:kate_y
ID: 12058452
Do we need to define DoCmd first. It gives me built-in error.

tks.

k
0
 
LVL 27

Expert Comment

by:planocz
ID: 12058561
did you add the reference to Access.dll?
0
 
LVL 27

Expert Comment

by:planocz
ID: 12058593
change to this also...

 .DoCmd.OutputTo(Access.AcOutputObjectType.acOutputQuery, "qry_month", "MicrosoftExcel(*.xls)", "D:\Recertifications.xls")
0
 

Author Comment

by:kate_y
ID: 12058741
how to make the reference to Access.dll. I was trying to import Access application but couldn't find a way. tks.
0
 
LVL 27

Accepted Solution

by:
planocz earned 500 total points
ID: 12058792
On your VS.net got to the solution explorer, right click on references then,
in window goto com tab and find the access object dll to select it.
0
 

Author Comment

by:kate_y
ID: 12058988
thank you so much. I got the reference. I am such a beginner. Sorry for the silly question.

Hopefully it will finally work out.
0
 

Author Comment

by:kate_y
ID: 12059222
Still some problem. Here is my code. Error says: need an open database.

Private Sub Export_Click()  

        Dim cnn As OleDbConnection
        Dim test As New Access.Application
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data       Source=C:\SCOPT\GPLogiModData-0815.mdb;"
        cnn = New OleDbConnection(connString)
       
        cnn.Open()
       
          test.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "TM_Scenario", "c:\SCOPT\temp.xls")

End Sub
0
 
LVL 27

Expert Comment

by:planocz
ID: 12063627
Try add this...

        test.OpenCurrentDatabase("C:\SCOPT\GPLogiModData-0815.mdb")
0
 

Author Comment

by:kate_y
ID: 12065642
It is working. Thank you so much...:)...
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:kate_y
ID: 12067218
Hi, I can successfully import the excel file to the Access database. but it duplicates the database as a locking information so that I have to go to the task manager to terminate the process otherwise couldn't open the original db.  Can anyone explain and how to solve it? thanks. here is my code for importing excel file.

Try
            test.OpenCurrentDatabase("C:\SCOPT\GPLogiModData-0815.mdb")
         
            test.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "TM_Calendor", "C:\SCOPT\ram.xls")
         
           MsgBox("Import successful!")

      .......

End Try
0
 
LVL 27

Expert Comment

by:planocz
ID: 12067634
add...

        test.Quit()
        test = Nothing
0
 

Author Comment

by:kate_y
ID: 12068284
hmm, still duplicate the db and couldn't open the old one. But the table has been added...strange..:(
0
 
LVL 27

Expert Comment

by:planocz
ID: 12068462
change to this...
 
test.CloseCurrentDatabase("C:\SCOPT\GPLogiModData-0815.mdb")
test.Quit()
test = Nothing


still duplicate the db ?

Are you looping twice thru database?
0
 

Author Comment

by:kate_y
ID: 12068643
not exactly duplicate. it seems to generate a empty db which is locked so the old one is locked too. How come when exporting from db to excel it doesn't do this?
0
 

Author Comment

by:kate_y
ID: 12068709
I sort of figured it out. It seems because of the folder that contains the db was open. thanks.
0
 
LVL 27

Expert Comment

by:planocz
ID: 12068770
ok :)
0
 

Author Comment

by:kate_y
ID: 12069686
New problems..:(...  when exporting to excel, I exported the entire table so the first row of excel is the field names from Access table, which is ok since I want a template. However, when importing back to db, it also imported the first row of the excel file which is not actual value I want to import. So I thought maybe set the field name = false and only import certain range of the worksheet. but it gave error saying field F1 is not existing in the destination table.

      test.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acImport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, "TM_Test", myOpenFileDialog.FileName, False, "WorkSheet!B2:I2")
 
How to set the table's field names as fixed field name to the excel file when exporting, so it won't be importing back.

tks.
0
 
LVL 27

Expert Comment

by:planocz
ID: 12073914
So far in my search you can not get around that column field headers.
One way you might do is when importing back to DB make your connection and
loop thru each record. but on the first input record skip over it since you know it is
going to be the  column field headers. Then you will have the rest of your data as a
regular input loop.
0
 

Author Comment

by:kate_y
ID: 12079452
Hi, I made it work. Just put Hasfieldname: = true which will get rid of the header of the excel file when importing to db table.

Another question, I want to insert a constant value (let's say a Scenario Name) into the db table which receives imported values from the excel file. I have a variable to hold the constant value. But i need to loop through the db table so that all the rows that contain the imported value will be added the constant value.

My plan is this: an original table with 3 fields, a temp table with only 2 of the 3 orginal fields to export and import data from excel, then append all the temp table fields into the orignial table plus adding the constant value to the rest field. It is like importing 100 records to the 2 fields and adding 100 constant values to the other fields. How to loop through the table to do the second insert statement? my SQL:


strSQL = "INSERT INTO Test_Calendar ( Period ) Select * FROM Ex_Calendar"
     DoCmd.RunSQL(strSQL)

for i=0 to ....???
strSQL1 = "INSERT INTO Test_Calendar ( ScenarioName ) VALUES('" & strScenarioName & "')"
     DoCmd.RunSQL(strSQL)

next

THANKS,
0
 
LVL 27

Expert Comment

by:planocz
ID: 12083418
The Membership Guidelines do not allow me to answer multiple questions.
If you can post your question I can try an answer it.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now