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

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

Do we need to define DoCmd first. It gives me built-in error.

tks.

k

did you add the reference to Access.dll?

change to this also...

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

how to make the reference to Access.dll. I was trying to import Access application but couldn't find a way. tks.

thank you so much. I got the reference. I am such a beginner. Sorry for the silly question.

Hopefully it will finally work out.

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

Try add this...

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

It is working. Thank you so much...:)...

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

add...

        test.Quit()
        test = Nothing

hmm, still duplicate the db and couldn't open the old one. But the table has been added...strange..:(

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


still duplicate the db ?

Are you looping twice thru database?

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?

I sort of figured it out. It seems because of the folder that contains the db was open. thanks.

ok :)

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.

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.

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,

The Membership Guidelines do not allow me to answer multiple questions.
If you can post your question I can try an answer it.