Access to Excel and Back

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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.

Code:
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...

VBA Code:
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
L

Legacy 456155

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

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,718
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 
L

Legacy 456155

Guest
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,958
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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 ..:)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,551
Messages
5,548,707
Members
410,866
Latest member
StuartAllison
Top