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

 

[silentwolf]

HI thanks again.
Data upload does not really work as the data is massiv and it need to run through many function to be able to have some decent column sitzes as they are up to 500 oddcharacters.

And that is why I like to insert those worksheets into one and than import this into my access table where I can run all functions to modify it to suit.
This code is already in place in access but when some data is for the first few fields less then 255 characters then the access asumes it is text. And then my codes are failing ..

Thats way I would like to kind of stick with what I like to do.

 

[Legacy 456155]

I understand your answer. What is not working at this point? The only thing we are missing is saving a copy of test.xlsm/wkbExcel as a copy and thus leaving test.xlsm as it is. Correct? Please post your current code with comments as to what you need done and what is not working. Thanks.

 

[silentwolf]

Hi thanks.
Sorry I need a few more minutes to get my code organised again. It is a pretty mass now with all the trying...
I even got to the point of writing it in excel vba and then run it from access but there is also problem with the code.. Its kind of anoying at times

 

[Legacy 456155]

I even got to the point of writing it in excel vba and then run it from access but there is also problem with the code.

Not a bad idea IMO. I've been doing that for years and I bet others here do the same thing.

 

[silentwolf]

Hi again,
good that what I am doing is not completly bad laugh
I will stick to that but today I am not that often in front of the computer so I will get back to you later.
I managed to get it all from a folder into my worksheet in one step as you said. But now I am trying to get access to run the code.
With the run command.. I still got problems.

Maybe you got a hint or a good tutorial for me I could try?

Many thank again!

Cheers

 

[Legacy 456155]

good that what I am doing is not completly bad laugh

Don't be so hard on yourself. Your doing great. Just enjoy it. May as well since you gotta get the job done. Right?

Maybe you got a hint or a good tutorial for me I could try?

I don't. Sorry. I don't really have the opportunity to work much with Excel but learn as the need arises. As far as Access, I work with that pretty much every day, but I don't know of any specific books or tutorials. They are all over the web, of course and I can generally find "something" that helps or points me in the right direction when I get stuck or am looking for a better way to solve a problem. When you get around to it, post away and we'll eventually get to "good enough." Have a good day!

 

[silentwolf]

Ok no problem ! Thanks anyway and I will post more when I get to the next stage and geting stuck ,)
Thanks again and also a great day !