Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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

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
kate_y
Asked:
kate_y
  • 11
  • 10
1 Solution
 
planoczCommented:
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
 
kate_yAuthor Commented:
Do we need to define DoCmd first. It gives me built-in error.

tks.

k
0
 
planoczCommented:
did you add the reference to Access.dll?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
planoczCommented:
change to this also...

 .DoCmd.OutputTo(Access.AcOutputObjectType.acOutputQuery, "qry_month", "MicrosoftExcel(*.xls)", "D:\Recertifications.xls")
0
 
kate_yAuthor Commented:
how to make the reference to Access.dll. I was trying to import Access application but couldn't find a way. tks.
0
 
planoczCommented:
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
 
kate_yAuthor Commented:
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
 
kate_yAuthor Commented:
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
 
planoczCommented:
Try add this...

        test.OpenCurrentDatabase("C:\SCOPT\GPLogiModData-0815.mdb")
0
 
kate_yAuthor Commented:
It is working. Thank you so much...:)...
0
 
kate_yAuthor Commented:
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
 
planoczCommented:
add...

        test.Quit()
        test = Nothing
0
 
kate_yAuthor Commented:
hmm, still duplicate the db and couldn't open the old one. But the table has been added...strange..:(
0
 
planoczCommented:
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
 
kate_yAuthor Commented:
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
 
kate_yAuthor Commented:
I sort of figured it out. It seems because of the folder that contains the db was open. thanks.
0
 
planoczCommented:
ok :)
0
 
kate_yAuthor Commented:
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
 
planoczCommented:
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
 
kate_yAuthor Commented:
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
 
planoczCommented:
The Membership Guidelines do not allow me to answer multiple questions.
If you can post your question I can try an answer it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now