220
We have created an Access database that collects transactions from an outside vendor. We would like to import these transactions into the 08.010.00. How can I manipulate the data for import?
Any help would be appreciated.
*This post is locked for comments
While your question has pretty much been answered (create a control macro, export from access to a properly formatted csv) there is another way that plays by the SL rules (not writing directly to the tables) which you might find useful. You could make a button on an access form and using the SL object model fill in the screen. This is for a Journal Entry, but it should be enough to get started.
sub makeentry()
If JournalEntry Is Nothing Then
Set JournalEntry = MyToolbar.StartApplication("0101000.EXE")
JournalEntry.Visible = True
End If
'get the levels
Set ctcompany = JournalEntry.Controls("ctcompany") 'header
Set cbatnbrH = JournalEntry.Controls("cbatnbrH") 'details
'new batch
strDataEntity = cbatnbrH.Properties("Level")
JournalEntry.New strDataEntity
JournalEntry.Controls("cautorev").Value = 0
JournalEntry.Controls("cperpostH").Value = perpost
JournalEntry.Controls("cledgerid").Value = "ALLOCATION"
strDataEntity = ctcompany.Properties("Level")
' JournalEntry.New strDataEntity
For i = 3 To 50
dbcr = xlSheet.Columns("N").Rows(i).Value
dbcr = Trim$(dbcr)
If dbcr = "db" Or dbcr = "cr" Then
acct = xlSheet.Columns("K").Rows(i).Value
acct = Trim$(acct)
subacct = xlSheet.Columns("L").Rows(i).Value
subacct = Trim$(subacct)
amount = xlSheet.Columns("M").Rows(i).Value
lngAmount = amount * 100
amount = lngAmount / 100
JournalEntry.New strDataEntity
JournalEntry.Controls("ctrandate").Value = VBA.Format$(transdate, "MM/DD/YYYY")
JournalEntry.Controls("cacct").Value = acct
JournalEntry.Controls("csub").Value = subacct
....
next I
JournalEntry.Save
I use the Transaction Import. I use Generate Control Macro to generate a macro that shows the fields on the screen and their possible values. I generate an Intelligent macro to show me the levels on the screen (which could be batch, document, detail or a separate tab) and then I generate a Simple macro to give me the headings for the columns in Excel - which is where I build my import.
The trick are the different levels and know how to set up your import spreadsheet. If you are making changes to Level 2 in a screen (your Intellegent macro will give more detail on the levels) the you would need replace the level name given to you in the Simple macro with the following: level2,change. You can then fill in just the data you need to import under the other headings for that level. Repeat for every level. In the end, your excel spreadsheet might look something like this after you save it to a .CSV file and open in Notepad (CSV is needed for import):
"level0,change",data,data,,,,,data.
"level1,change",,,,data,,,data,,data,
The commas represent columns you did not fill in with data. Transaction Import will not make changes to or import any value into those fields.
Please note - you generally do not need to include the quatation marks around the 'level0,change' text but make sure they are present when you save as a .CSV file. The magically appear when you.
I hope this is helpful and what you were looking for. This is years of hit and miss with Transaction Import knowledge. I don't know eveything yet but the Error log is a good guide to figuring out what the issue is if the import fails.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (