asked on

Export Data from SAGE via ODBC to Excel (and then update via macro)

Hello,

I am using Sage Line 50 on a 64 bit machine runnings Winfows 7. I want to export data from SAGE to Excel. I need data from what I be three reports / tables in SAGE:-

1) A list of all sales items by product - showing:-
i) the date of the sale
2) the items sold
3) the number sold
4) the price of the items
5) the total revenue

2) A list of all the operational costs by date

3) the trial balance

I have already set up the ODBC connection between SAGE and Excel. However I can't seem to create the data extract within excel. At this point, I only need to use the practice data in SAGE but will later on need to switch to the true data.

Also I would like to implement a button as a Macro to pull in all the data. Thanks

Regards
Kaps

Bruce Denney

This would do it.

Private Sub CommandButton1_Click()

Dim qt As QueryTable
Dim wks As Worksheet

wks = "Worksheet to update"

For Each qt In wks.QueryTables
	With qt
	.Refresh BackgroundQuery:=False
	End With
Next qt

End Sub

Open in new window

Kap68

ASKER

Thanks. I have made some progress but I have discovered that I need to join data from two tables. I have used the SQL code generator in SAGE and imported into Excel but am having problems getting the exact notation correct:-

Sheets("Invoice Line Items").Activate
Sheets("Invoice Line Items").Range("A1:pp20000").ClearContents
  
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=SageLine50v19;UID=Manager;;", Destination:=Range( _
        "'Invoice Line Items'!$a$1")).QueryTable
        .CommandText = Array( [b]"SELECT INVOICE.INVOICE_DATE, INVOICE_ITEM.QUANTITY, INVOICE_ITEM.DESCRIPTION, INVOICE_ITEM.UNIT_PRICE, INVOICE_ITEM.GROSS_AMOUNT _
FROM INVOICE INVOICE, INVOICE_ITEM INVOICE_ITEM _
WHERE INVOICE_ITEM.INVOICE_NUMBER = INVOICE.INVOICE_NUMBER ")[/b]
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_ExternalData_2"
        .Refresh BackgroundQuery:=False
End With

Open in new window

I need the invoice date from the invoice table on each line of my invoice line item extract. can you tell me what I am doing wrong ? thanks

Kaps

Bruce Denney

You don't need to do anything in the VB.

Just setup the ODBC link in Excel, I would use MSQuery to join the tables.

VB is only used to refresh the data, which you could do manually by clicking refresh.

I would get your link setup and working with a manual refresh, then automate the refresh using the VB I provided earlier.

Kap68

ASKER

Thanks - I have tried to do that - but I tried to simulate a refresh by clearing the data from excel and rerunning the macro, the sheet was not refreshed. Here is the code I have:

Public Sub extract_data()
  
Dim qt As QueryTable
Dim wks As Worksheet

Set wks = Sheets("invoice line items")

For Each qt In wks.QueryTables
    With qt
    .Refresh BackgroundQuery:=False
    End With
Next qt
  
  

With Sheets("invoice line items").Range("a1:a10000")
    .NumberFormat = "dd/mm/yyyy"
End With

With Sheets("invoice line items").Range("b1:b10000")
    .NumberFormat = "0"
End With

With Sheets("invoice line items").Range("d1:e10000")
    .NumberFormat = "£0.00"
End With


End Sub

Open in new window

Bruce Denney

Can you confirm

1/ when you click refresh on the sheet it updates itself and loads data from Sage?

2/ the sheet you have with the query on it is called "invoice line items"

(I would avoid spaces in names by naming the sheet without Spaces eg "InvoiceLineItems" this is because invoice line items and invoice line items and invoice line items all look very similar)

Kap68

ASKER

Thanks I have managed to get it working by extracting the data from SAGE tables. I have posted my code below. However I can't seem to locate the info for the cost of sales side - which table should i be using to get this info ?

Sheets("invoice line items").Activate


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=SageLine50v19;UID=Manager;;", Destination:=Range( _
        "'Invoice Line Items'!$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT INVOICE.INVOICE_DATE, INVOICE.INVOICE_NUMBER, INVOICE_ITEM.DESCRIPTION, INVOICE_ITEM.QUANTITY, INVOICE_ITEM.UNIT_PRICE, INVOICE_ITEM.GROSS_AMOUNT" & Chr(13) & "" & Chr(10) & "FROM INVOICE INVOICE, INVOICE_ITEM INVOICE_ITE" _
        , "M" & Chr(13) & "" & Chr(10) & "WHERE INVOICE_ITEM.INVOICE_NUMBER = INVOICE.INVOICE_NUMBER")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_ExternalData_1"
        .Refresh BackgroundQuery:=False
    End With

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.