Access to Excel and Back

[silentwolf]

Hi guys,

sorry for the thread title but I haven't been able to think of a good one.
However here is where I am gettting stuck at.

At my previous posts I have been working on importing different worksheets into one and import this sheet into access.

What I got so far.

In Access I created a userform with a txtStatement(Textbox) and a btnBrowse(command button) and one btnTest(commandButton)
With fileDialog I am able to pickFolder where different excel workbook files are located.

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant

    Set diag = Application.FileDialog(msoFileDialogFolderPicker)    'Folder Picker

    With diag
        .AllowMultiSelect = False
        .Title = "Wählen Sie den Folder aus!"
        .InitialFileName = p_cstrCSV_Verzeichnis                'public const_FilePath
            
        If diag.Show And .SelectedItems.count > 0 Then
            Me.txtStatement.Value = .SelectedItems.item(1)
        End If
    End With
    Set diag = Nothing
End Sub

The if statement I might could change a bit as it was from the code bevore where I picked a file...
You can let me know if there is a better way of doing this...

Private Sub btnTest_Click()
    Dim xl As Excel.Application
    Dim wkbExcel As Excel.Workbook
    Dim strFolderPath As String
    
    Set xl = CreateObject("Excel.Application")

    xl.Workbooks.Open "C:\Users\Documents\My Data Projects\Auszugsklasse\AJL_Statement.xlsm", True, False
    
    strFolderPath = Me.txtStatement.Value
    
    strFolderPath = strFolderPath & "\"
    
'-------------------------------------------------------------------
    xl.Run "InsertStatementsFromFolderPath", strFolderPath

    xl.Visible = True

    Set xl = Nothing
End Sub

The above code works fine it opens the workbook and then run Code from that specific workbook with the xl.Run "InsertStatementsFromFolderPath" strFolderPath procedure

So so far the code is working fine and I am opening the workbook where all workbooks within the folder got inserted into ThisWorkbook in the first Position. Only one worksheet is in that workbook!

So far so good. However this is not quite finished yet.

I like to insert this worksheet in the access database. So how should I go about it? How to I get the worksheet into my access Form so when I am using the btnTest_click event to do that for me.

How am I able to create a varible so Access knows what to import.

It is most proberbly easy but I am running in circles at the moment .

Hope you guys understand what I am after and just can put me on the right track.

Many thanks

 

[Legacy 456155]

Hi silentwolf. Are you able to link your spreadsheet from within Access? Or have you created an import from within Access?

 

[silentwolf]

Hi Dataluver,
well when I have done it with filepicker I was able to insert the data into access. Got all in place so to speak but not with the folder.
First I start with Access and then I run an Excel workbooks Code from Access. After the workbook has run its code I am getting lost.

How can I get the worksheet name back into my access code .. I guess that is the big question.

 

[silentwolf]

Thanks for your reply sorry

 

[xenou]

If you want to load a worksheet it is usually best to give it a standard and unchanging name, then you can load by names. If there is danger of the name changing then you might have to use Excel vba to get the name of the worksheets in the workbook instead.

 

[Legacy 456155]

Have you considered what xenou has stated? Is this something that you can do? If not, please post all of the relevant Access code. It's not difficult to get the sheet name if you must. How exactly are you importing the data into Access?

 

[Micron]

How can I get the worksheet name back into my access code

That was answered for you?

Are you able to link your spreadsheet from within Access?

You link to the worksheet via Get External Data (ribbon).
Not sure why you want to run an Excel macro from Access if you're just consolidating Excel data manually (or at least from within Excel) and then could just link to the consolidated worksheet from Access. That is, as long as the sheet name never changes; Access will always show the up to date data. If you must get the data into Access, did you look at doing Transferspreadsheet method?

 

[silentwolf]

Hi guys,
just a quick thank you to all of you!

I am trying at the moment to sort it out but will get back to you as soon I have hopefully some news well good news ..