Referenz to Excel from Access

[silentwolf]

Hi Guys,

I got troubles with the following code wich I like to run from Access.

Could someone please look over this code and let me know what I need to change in order to correct instanciate to Excel from Access?

Manly I got problems where the code is ============================ marked.
But perhaps it would be good to look quickly over the code.

Sub InsertWorksheetsFromFolder()
    Dim appExcel As Excel.Application
    Dim wkbExcel As Excel.Workbook
    
    Dim strPath As String
    Dim strFileName As String
  
    Dim Bereich As Range
    Dim strLC As String
    Dim i As Integer
      
    Set appExcel = New Excel.Application
    Set wkbExcel = appExcel.Workbooks.Open("C:\Users\Silentwolf\Documents\My Data Projects\MyFiles\Test.xlsm")
    
    appExcel.Visible = True
    
    appExcel.Application.ScreenUpdating = False

    strPath = "C:\Users\Silentwolf\Documents\Files\2019\Statements\"
    strFileName = Dir(strPath & "*.xlsx")

    Do While strFileName <> ""
        appExcel.Workbooks.Open FileName:=strPath & strFileName, ReadOnly:=True
        
        With appExcel.ActiveWorkbook
            .Worksheets(1).Copy After:=wkbExcel.Sheets(1)
        End With
        
        appExcel.Workbooks(strFileName).Close
        strFileName = Dir()
    Loop
 
 '=========================================================   
    For i = 2 To wkbExcel.Worksheets.count
        With wkbExcel.Worksheets(i).UsedRange
            strLC = .Cells(.Rows.count, .Columns.count).Address
            Set Bereich = wkbExcel.Range("A2:" & strLC)
            Bereich.Copy Destination:=Sheets("Zusammenfassung").Cells(Rows.count, 1).End(xlUp).Offset(1, 0)
        End With
    Next i
 ' ======================================================= 
    appExcel.Application.ScreenUpdating = True
        
    Stop
    
    appExcel.Quit
    Set wkbExcel = Nothing
    Set appExcel = Nothing
    
End Sub

Many thanks

 

[Legacy 456155]

Bereich.Copy Destination:=Sheets("Zusammenfassung").Cells(Rows.count, 1).End(xlUp).Offset(1, 0)

That one will most definitely give you a headache.

Change it to:
wkbExcel.Sheets("Zusammen...

Maybe try:
Set Bereich = Range([a2], Cells.SpecialCells(xlCellTypeLastCell))

 

[silentwolf]

Hi Dataluver,
many thanks for your reply.
I did change as above mentioned from you but it only works for the first time around.
If I run the prozedure again without closing the access db I get an error either at the line ......Set Bereich = Range([a2]) and it does not make a difference if I write it your way or the way I did have it.
Or the error occurs at the nex line Bereich.Copy Destination:=wkbExcel.Sheets("Zusammen...

So is there another way of getting rid of this error ?

Many thanks

 

[Legacy 456155]

Sorry. I did the same thing that I noted in your code to begin with.

I'm assuming that the worksheet "Zusammenfassung" is in test.xlsm.

'=========================================================
    For i = 2 To wkbExcel.Worksheets.Count
        With wkbExcel.Worksheets(i).UsedRange
            strLC = .Cells(.Rows.Count, .Columns.Count).Address
            Set Bereich = .Parent.Range("A2:" & strLC)
            With wkbExcel.Sheets("Zusammenfassung")
                Bereich.Copy Destination:=.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            End With
        End With
    Next i
' =======================================================

Also, I don't see where you are closing and saving test.xlsm. Your code, as is, will likely leave a hidden Excel process running. You should probably have a close.save somewhere further down in your code before you quit the app.

wkbExcel.Close True

The true is save changes.

Let's see where this gets us.

 

[silentwolf]

https://www.access-o-mania.de/forum/index.php?topic=24568.0

For your information I did try my luck also in a german access forum ..
Hope that is ok?

 

[silentwolf]

Hi Dataluer,
I'm assuming that the worksheet "Zusammenfassung" is in test.xlsm. ...........Yes it is..


'=========================================================
For i = 2 To wkbExcel.Worksheets.count
With wkbExcel.Worksheets(i).UsedRange
strLC = .Cells(.Rows.count, .Columns.count).Address
Set Bereich = .Range("A2:" & strLC)
With wkbExcel.Sheets("Zusammenfassung")
Bereich.Copy Destination:=wkbExcel.Sheets("Zusammenfassung").Cells(Rows.count, 1).End(xlUp).Offset(1, 0)
End With
End With
Next i

wkbExcel.SaveAs (strPath & "Test03")
wkbExcel.Close

appExcel.Application.ScreenUpdating = True

appExcel.Quit
Set wkbExcel = Nothing
Set appExcel = Nothing

'=======================================================================

this is how I got it now... ) getting better .. but is it good like this?
But I like to save the new created or better the updated file seperatly..

In other words.. To beginn with the file should act as template.. then I insert different worksheets form different workbooks into that file and then
fill all worksheets into "Zusammenfassung"..

The other worksheets are not needet anymore after insert into worksheet("Zusammenfassung"). can be deletet and the newly "created" file will be then saved as "Zusammenfassung_2019.xlsx" or
something like that.
The main workbook can be closed.

Hope this makes sence

 

[silentwolf]

Hi again,
and the way I got it at present there will be a excel left open in the task manager...(

 

[Legacy 456155]

In other words.. To beginn with the file should act as template.. then I insert different worksheets form different workbooks into that file and then
fill all worksheets into "Zusammenfassung"..

Hello silentwolf. Where are we at on this? I can try to help you, but I need a clear understanding.

C:\Users\Silentwolf\Documents\My Data Projects\MyFiles\Test.xlsm
This represents your "template?"

C:\Users\Silentwolf\Documents\Files\2019\Statements\
This folder represents the location of various files, each containing one worksheet which contain data that you wish to copy into a NEW file with a sheet named "Zusammenfassung?"

Why are we copying the worksheets to begin with? It seems that you are copying worksheets, then taking data from each into Zusammenfassung, and then you no longer need the copied worksheets. Do I understand? You could just get the data directly from each worksheet where it is.

I'm not completely clear about your process. Probably because I have not had my yet.

 

[silentwolf]

Hi Dataluver,
many thanks for your reply!!
Yes I do use the file as a template but not a saved as a template..
And yes there is a folder where all the same kind of files are located and they need to get inserted to one file well one worksheet.
So you are completly right

I get each month different workbooks with one worksheet. All of those files are completly the same accept the data of course
Now I need it into one file but need to modify that file "worksheet" so it can be importet to access.
The reason for this that access fields depanding on what info my cells have it may just creates a text file rather then a long text field.
And one other column need to be in text rather then numbers..
if all that works than I like to import the new file into access.. where I do further my things... )
Hope this is a bit clearer now?

Cheers .)

 

[Legacy 456155]

Well that might open up some other options. Any chance that you can upload an example of one of your data files to this folder. You can use ADO to query well structured Excel files and update your tables using the resulting recordset. This might be easier than you think. If you want to try this method and if you are able, upload a simple accdb with the table or tables that receive the import. There does not have to be any existing data in the tables.