Naming a range to export data to an access table.

[LOUISBUHAGIAR54]

I am trying to write code in an excel worksheet to try to export excel data to a table in access.

The range in which the data is situated in excel varies. So the range address varies with the data. After a lot of searching I wrote the following code which works for a range which is fixed. The code is the following:

Dim acc As New Access.Application
Range(Range("b22"), Range("b22").End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection

acc.OpenCurrentDatabase "D:\PayRollFactory\JasminePayroll.accdb"

acc.DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:="WorkItems", _
Filename:=Application.ActiveWorkbook.FullName, _
HasFieldNames:=True, _
Range:="Sheet2$B140"

acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing

End Sub

---------------------

As one can see from the first two lines of code I am trying to first delineate the range and then to name it. The intent is to name the range "ghazla" and so to cater for the fact that the extent of data varies.

The code works with the fixed range ="Sheet2$B140". However what I need to do is to adjust the code so that I can insert "ghazla" as the range name so that this can vary.

Can someone please give a hand. I know that this is not access code but I am sure someone has had this problem before. Sorry for the grin but it does not seem to go away. The range is from B1 to D40 although it does not really matter much.

Many thanks.

Louis Buhagiar

 

[Trevor G]

You don't have to use a named range for the final part you can just use the Worksheet name and add an exclamation mark at the end as it will recognise the sheet name as the data you want to upload. Look at this example. This is the Access Macro converted to VBA, so it shows the Import method.

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 10, "tblStaff", "C:\Access\Export.xlsx", True, "qryBetweenDates!"
    DoCmd.TransferSpreadsheet acImport, 10, "tblData", "C:\Access\Export.xlsx", True, "Staff!"

 

[spikepl]

In Excel a named range can have the scope of datasheet or global - the entire workbook. If you don't know what I'm talking aboiut open an Excel workbook and in a sheet manually create a named range.

A global range can be referred to directly in the TranferSpreadsheet. You just supply its name in the Range parameter, without the need to supply the name of any specific sheet

 

[LOUISBUHAGIAR54]

Many thanks for your helpful answers. I must admit I am not an expert with access vba although I am managing ok with a lot of research. I got my code from the web and copied it into my program. If I were to change the DoCmd. line to the format indicated by you I will encounter difficulties with inputting the address of the excel file address.

So my next question is how do I input the range name "ghazla" into the format shown in my original code. I tried imputing in "ghazla!" but the code sticks when it comes to interpreting that particular line.

Any help.....or else how do I input the excel file address into the code as indicated by you as a variable (in case the address of the excel file changes.)

Many thanks once again


Louis Buhagiar

 

[Brianwarnock]

First the this is a smilie with the code , it is always a problem in Excel but you can disable smilies in text when posting, it is I additional options beneath the submit.

can you not name the relevant area by

Dim lrsh as long

lrsh = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=ActiveSheet.Range("B1:D" & lrsh)

you may prefer other methods for finding the lastrow and change the reference from activesheet to the relevant sheet

Brian

PS I don't have ACCESS so my help is limited

 

[LOUISBUHAGIAR54]

Many thanks. The problem is not naming the range as this seems to be working well. The problem is to introduce the name of the range "ghazla" so that the DoCmd.TransferSpreadsheet command works. I would like to introduce the name of the range into the command in my code shown originally, so that I would not have to change anything else.

Anyone with more help in excel vba code.


LOUIS BUHAGIAR.