Solved

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

Posted on 2004-09-14
21
437 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

914 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

17 Experts available now in Live!

Get 1:1 Help Now