Transfer Spreadsheet Automation 1

[sxschech]

I am using this code to bring in excel data. I'd like to "enhance" it as currently I have to switch to excel to change a cell number so that the data set I want to import is "refreshed".

Private Sub btnImportExcel_Click()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DataSet2006", "\\Discimageserver\DataStats\2006Conversion.XLS", True, "DataSet2006"
End Sub

In excel I have a number in Cell H1. Each time I click the import button on the Access form, I would like to be able to choose a number, enter it in a text box on the Access form and have that number placed into cell H1 of the spreadsheet so that the transfer spreadsheet code will pick up the selected data. The named range in excel is called DataSet2006. There are three cols that are affected by the number reflected by Cell H1. For example if I type a 5 in cell H1, then the formulas within the Named Range will look at data in Col P (The 5th col from the start point [Col L].

=(INDEX($L$2:$IV$2,1,$H$1))
=(INDEX($L$3:$IV$3,1,$H$1))
=(INDEX($L4:$IV4,1,$H$1))

The access button and excel formula are working fine, but I have to switch to excel, change the number, then switch back to access click on the import button I created, then swicth back to excel, so if I can refer or pass the number needed by Excel in cell H1, then the process can be quicker.

I am using this to transpose a lot of data into a simple table. If this can be done, then perhaps it could be further enhanced with a loop so that if I have 50 cols of data, I can have it start cell H1 with the number 1 and then increment to 2, 3, 4..,50.


I already tried a pivot table, but it didn't quite seem to give me what I needed, or I didn't quite know how to fully use it. Thread: Pivot Tables Multiple Consolidation Ranges

http://www.tek-tips.com/viewthread.cfm?qid=1445984

 

[PHV]

Have you tried to use OLE Automation ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxschech]

PHV I looked around for the automation information and found some examples at the Access Web site (

http://www.mvps.org/access)

but my knowledge is limited in this area. I found some code that sort of worked, tried to modify it, but couldn't figure out how to get it to work for my situation. The code would open up new instances of excel and then close it and then a message would pop up saying that the file is ready for editing. The screen wouldn't refresh and I would then have to manually close excel.

 

[PHV]

Without seeing your code it's hard to say anything ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxschech]

I did a bit more searching and found a site that explained a bit more. I cobbled this code together, not the most efficient, but it did the job.

Sub historical()
    'Excel automation portion of this code came from
    '[URL unfurl="true"]http://www.vbforums.com/showthread.php?t=391665[/URL]
    'Added looping on 30 Jan 2008
    
    Dim x As Integer
    x = 1
    Do While x < 64
          Dim oXLApp As Excel.Application         'Declare the object variables
          Dim oXLBook As Excel.Workbook
          Dim oXLSheet As Excel.Worksheet
                
          Set oXLApp = New Excel.Application    'Create a new instance of Excel
          'Set oXLApp = GetObject(, "Excel.Application") 'Use an already open instance of Excel
          Set oXLBook = oXLApp.Workbooks.Open("\\Discimageserver\Stats\2006Conversion.xls") 'Open an existing workbook
          'Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
          Set oXLSheet = oXLBook.Worksheets(2)  'Work with the second worksheet
              
          oXLSheet.Cells(1, 8).Value = x 'Forms!frmMain.txtMaxWeek
        
          Set oXLSheet = Nothing             'disconnect from the Worksheet
          oXLBook.Close SaveChanges:=True    'Save (and disconnect from) the Workbook
          Set oXLBook = Nothing
          oXLApp.Quit                        'Close (and disconnect from) Excel
          Set oXLApp = Nothing
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DataSet2006", "\\Discimageserver\Stats\2006Conversion.XLS", True, "DataSet2006"
          x = x + 1
      Loop
      MsgBox "Finished Importing Data", vbOKOnly
End Sub

Thanks for pointing me in the right direction.

 

[JerryKlmns]

Just moved the creation of Excel outside of the loop. You create it once and use it throught out the loop, then destroy it.

Sub historical()
'Excel automation portion of this code came from
'[URL unfurl="true"]http://www.vbforums.com/showthread.php?t=391665[/URL]
'Added looping on 30 Jan 2008
    
Dim x As Integer
x = 1
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook

Set oXLApp = New Excel.Application    'Create a new instance of Excel

Do While x < 64
     Set oXLBook = oXLApp.Workbooks.Open("\\Discimageserver\Stats\2006Conversion.xls")               
oXLBook.Worksheets(2).Cells(1, 8).Value = x         
oXLBook.Close ,True    'Save (and disconnect from) the Workbook
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DataSet2006", "\\Discimageserver\Stats\2006Conversion.XLS", True, "DataSet2006"
     x = x + 1
Loop
Set oXLBook = Nothing
oXLApp.Quit         'Close (and disconnect from) Excel
Set oXLApp = Nothing
MsgBox "Finished Importing Data", vbOKOnly

End Sub

 

[NWildblood]

sxschech & JerryKlmns thanks for this, you've saved me hours on a similar project.

"No-one got everything done by Friday except Robinson Crusoe...