Home

exporting multiple excel workbook with different names into access database

%3CLINGO-SUB%20id%3D%22lingo-sub-671900%22%20slang%3D%22en-US%22%3Eexporting%20multiple%20excel%20workbook%20with%20different%20names%20into%20access%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-671900%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Experts%2C%3C%2FP%3E%3CP%3Ei%20am%20a%20telecom%20business%20financial%20analyst%20and%20i%20work%20on%20daily%20basis%20with%20excel%20for%20assessing%20financial%20viability%20of%20different%20offers%20to%20be%20offered%20to%20market.%20I%20usually%20receive%20the%20offers%20from%20different%20pricing%20teams%20in%20excel%20sheets%20tables%20in%20the%20following%20format%3A%3C%2FP%3E%3CP%3EPrice%26nbsp%3B%20%26nbsp%3B%20Local%20minutes%26nbsp%3B%20%26nbsp%3B%20International%20Minutes%26nbsp%3B%20%26nbsp%3B%20Data%20(GBs)%26nbsp%3B%20%26nbsp%3B%20Customer%20Name%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BBusiness%20Case%20Ref%3C%2FP%3E%3CP%3E100%24%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1000%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B200%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20XYZ%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%205555%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDue%20to%20the%20massive%20numbers%20of%20offers%20(assessments)%20we%20do%2C%20we%20did%20not%20have%20a%20single%20database%20to%20track%20all%20the%20offers%20that%20has%20been%20assessed%20(based%20on%20above%20table%20format).%20Accordingly%2C%20we%20have%20decided%20to%20put%20an%20Access%20Database%20to%20record%20and%20track%20these%20offers.%20The%20solution%20worked%20quite%20will.%20However%2C%20in%20order%20to%20maximise%20the%20time%20benefit%2C%20i%20thought%20of%20why%20not%20to%20put%20a%20command%20(Macro%20based)%20in%20the%20excel%20sheet%20of%20each%20business%20case%20assessed%20which%20will%20automatically%20export%20the%20above%20offer%20table%20in%20the%20same%20format%20into%20the%20Access%20Database.%20the%20challenge%20which%20i%20need%20help%20for%20is%20the%20following%3A%3C%2FP%3E%3CP%3E1-%20how%20do%20i%20create%20a%20command%20(VBA%20based)%20to%20export%20the%20data%20in%20the%20offer%20table%3F%3C%2FP%3E%3CP%3E2-what%20is%20the%20code%20to%20be%20written%3C%2FP%3E%3CP%3E3-should%20i%20save%20the%20excel%20workbooks%20with%20single%20unique%20names%20since%20each%20offer%20is%20customised%20for%20each%20customer%20with%20a%20unique%20Business%20Case%20reference%20number.%20How%20do%20i%20overcome%20this%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20your%20kind%20support.%3C%2FP%3E%3CP%3EAyla%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-671900%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672244%22%20slang%3D%22en-US%22%3ERe%3A%20exporting%20multiple%20excel%20workbook%20with%20different%20names%20into%20access%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355353%22%20target%3D%22_blank%22%3E%40ayla-mamesh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Ayla%2C%3C%2FP%3E%3CP%3Ehere%20is%20a%20code%20from%20my%20german%20vba%20tanker.%20I%20think%20you%20can%20adopt%20it%20to%20your%20task.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20DatenAusExcelTabelleHinzuf%C3%BCgen()%3CBR%20%2F%3EDim%20conn%20As%20New%20ADODB.Connection%3CBR%20%2F%3EDim%20rst%20As%20ADODB.Recordset%3CBR%20%2F%3EDim%20objXlApp%20As%20Object%3CBR%20%2F%3EDim%20objMappe%20As%20Object%3CBR%20%2F%3EDim%20intZ%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20GoTo%20Fehler%3CBR%20%2F%3ESet%20objXlApp%20%3D%20CreateObject(%22Excel.Application%22)%3CBR%20%2F%3ESet%20objMappe%20%3D%20objXlApp.workbooks.Open(Application.CurrentProject.Path%20_%3CBR%20%2F%3E%26amp%3B%20%22%5CMitarbeiter.xls%22)%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20conn%20%3D%20CurrentProject.Connection%3CBR%20%2F%3ESet%20rst%20%3D%20New%20ADODB.Recordset%3CBR%20%2F%3E%3CBR%20%2F%3Erst.Open%20%22Personal%22%2C%20conn%2C%20adOpenKeyset%2C%20_%3CBR%20%2F%3EadLockOptimistic%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20intZ%20%3D%202%20To%20objMappe.sheets(%22Personal%22).usedrange.rows.Count%3CBR%20%2F%3EWith%20rst%3CBR%20%2F%3E.AddNew%3CBR%20%2F%3E.Fields(%22Nachname%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%202).Value%3CBR%20%2F%3E.Fields(%22Vorname%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%203).Value%3CBR%20%2F%3E.Fields(%22Position%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%204).Value%3CBR%20%2F%3E.Fields(%22Anrede%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%205).Value%3CBR%20%2F%3E.Fields(%22Geburtsdatum%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%206).Value%3CBR%20%2F%3E.Fields(%22Einstellung%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%207).Value%3CBR%20%2F%3E.Fields(%22Stra%C3%9Fe%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%208).Value%3CBR%20%2F%3E.Fields(%22Ort%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%209).Value%3CBR%20%2F%3E.Fields(%22Region%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%2010).Value%3CBR%20%2F%3E.Fields(%22PLZ%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%2011).Value%3CBR%20%2F%3E.Fields(%22Land%22)%20%3D%20objMappe.sheets(1).cells(intZ%2C%2012).Value%3CBR%20%2F%3E.Update%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ENext%20intZ%3CBR%20%2F%3E%3CBR%20%2F%3Erst.Close%3CBR%20%2F%3EobjMappe.Close%3CBR%20%2F%3EobjXlApp.Quit%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20objMappe%20%3D%20Nothing%3CBR%20%2F%3ESet%20objXlApp%20%3D%20Nothing%3CBR%20%2F%3ESet%20rst%20%3D%20Nothing%3CBR%20%2F%3ESet%20conn%20%3D%20Nothing%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EFehler%3A%3CBR%20%2F%3EMsgBox%20Err.Number%20%26amp%3B%20%22%20%22%20%26amp%3B%20Err.Description%3CBR%20%2F%3ESet%20objMappe%20%3D%20Nothing%3CBR%20%2F%3ESet%20objXlApp%20%3D%20Nothing%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%20-%20a%20database%20full%20of%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
ayla-mamesh
Occasional Visitor

Dear Experts,

i am a telecom business financial analyst and i work on daily basis with excel for assessing financial viability of different offers to be offered to market. I usually receive the offers from different pricing teams in excel sheets tables in the following format:

Price    Local minutes    International Minutes    Data (GBs)    Customer Name     Business Case Ref

100$         1000                     200                            2                      XYZ                          5555

 

Due to the massive numbers of offers (assessments) we do, we did not have a single database to track all the offers that has been assessed (based on above table format). Accordingly, we have decided to put an Access Database to record and track these offers. The solution worked quite will. However, in order to maximise the time benefit, i thought of why not to put a command (Macro based) in the excel sheet of each business case assessed which will automatically export the above offer table in the same format into the Access Database. the challenge which i need help for is the following:

1- how do i create a command (VBA based) to export the data in the offer table?

2-what is the code to be written

3-should i save the excel workbooks with single unique names since each offer is customised for each customer with a unique Business Case reference number. How do i overcome this issue?

 

Appreciate your kind support.

Ayla

1 Reply

@ayla-mamesh 

Hi Ayla,

here is a code from my german vba tanker. I think you can adopt it to your task.

 

Sub DatenAusExcelTabelleHinzufügen()
Dim conn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim objXlApp As Object
Dim objMappe As Object
Dim intZ As Integer

On Error GoTo Fehler
Set objXlApp = CreateObject("Excel.Application")
Set objMappe = objXlApp.workbooks.Open(Application.CurrentProject.Path _
& "\Mitarbeiter.xls")

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "Personal", conn, adOpenKeyset, _
adLockOptimistic

For intZ = 2 To objMappe.sheets("Personal").usedrange.rows.Count
With rst
.AddNew
.Fields("Nachname") = objMappe.sheets(1).cells(intZ, 2).Value
.Fields("Vorname") = objMappe.sheets(1).cells(intZ, 3).Value
.Fields("Position") = objMappe.sheets(1).cells(intZ, 4).Value
.Fields("Anrede") = objMappe.sheets(1).cells(intZ, 5).Value
.Fields("Geburtsdatum") = objMappe.sheets(1).cells(intZ, 6).Value
.Fields("Einstellung") = objMappe.sheets(1).cells(intZ, 7).Value
.Fields("Straße") = objMappe.sheets(1).cells(intZ, 8).Value
.Fields("Ort") = objMappe.sheets(1).cells(intZ, 9).Value
.Fields("Region") = objMappe.sheets(1).cells(intZ, 10).Value
.Fields("PLZ") = objMappe.sheets(1).cells(intZ, 11).Value
.Fields("Land") = objMappe.sheets(1).cells(intZ, 12).Value
.Update
End With
Next intZ

rst.Close
objMappe.Close
objXlApp.Quit

Set objMappe = Nothing
Set objXlApp = Nothing
Set rst = Nothing
Set conn = Nothing
Exit Sub

Fehler:
MsgBox Err.Number & " " & Err.Description
Set objMappe = Nothing
Set objXlApp = Nothing
End Sub

 

Best regards from germany

Bernd

www.vba-tanker.com - a database full of macros