Store date from cell in spreadsheet to a temporary variable

[Delindan]

This seems like it should be really simple but I can't figure it out. The format of the report that I receive has the date in one cell at the top and the remainder of the data in another place so I would like to grab that date and store it to a variable to that when I import the data to my table I can add the date to all the records. I've always used the transferspreadsheet which doesn't seem like it would work in this situation. Any ideas? Thanks!

 

[Delindan]

So I decided to try and create a named range so that I could just import it and be able to grab the date that way and am having trouble with the syntax. I recorded a macro in excel and copied and pasted so I was thinking it would be simple...not. Here's what I have:

With xlApp
.Visible = False
Set WB = .Workbooks.Open(strFileName)
Application.Goto Reference:="R54C2"
ActiveWorkbook.Names.Add Name:="info", RefersToR1C1:="=Sheet1!R54C2"
ActiveWorkbook.Names("info").Comment = ""
.Workbooks(1).Save
.Workbooks(1).Close
End With

It errs out on the application.goto line. Any thoughts...am I going in the wrong direction?

Thanks!

 

[SkipVought]

you already have a reference to the Excel APPLICATION...

With xlApp
  .Visible = False
  Set WB = .Workbooks.Open(strFileName)
  .sheets(1).Goto Reference:="R54C2"
  wb.Names.Add Name:="info", RefersToR1C1:="=Sheet1!R54C2"
  wb.Names("info").Comment = ""
  wb.Save
  wb.Close
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

What about this ?

With xlApp
 .Visible = False
 Set WB = .Workbooks.Open(strFileName)
 .Goto Reference:="R54C2"
 WB.Names.Add Name:="info", RefersToR1C1:="=Sheet1!R54C2"
 WB.Names("info").Comment = ""
 WB.Save
 WB.Close
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

the report [...] has the date in one cell at the top [...] I would like to grab that date and store it to a variable

Where, which cell, is the date you want to 'grab'?

Have fun.

---- Andy

 

[Delindan]

I receive a report in excel each month that has data I am importing to a temporary table in access. The report has one date in a particular cell (B54) that is the same location each month. In my routine I would like to import the data to the temp table then append all records to the permanent with the date that is in this cell. I've got everything working except the date. What would be great is to have a variable that I could somehow store the contents of the cell (B54) into so that I could use the variable in my append routine. The only way I've grabbed data from excel is through a docmd.transferspreadsheet command. It doesn't work so nicely for one cell of information though. Any thoughts?

 

[sxschech]

I'm grabbing a date from an excel file, but did it without code as at the time I set it up didn't know how to code it. What I did was save the excel file or rename it to a standard name each week and then linked the cell via an sql statement. Then I did a dlookup to grab the date for use in queries, code, etc.

Here is a sample of the sql statement:

SELECT T1.* 
FROM [Excel 8.0;HDR=NO;IMEX=1;Database=c:\temp\datafile.xls].[Sheet1$B1:B1] as T1;

I found this from:

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpSQL

 

[SkipVought]

Ths workbook that you receive:

Are there multiple worksheets or just one sheet?

If just one, isn't that data in your temp table after import?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Delindan]

I had been importing just the select range that had the records of interest in them. I just decided prior to your post that I should just import everything and then should be able to grab the date that way. I'm still working out kinks here but I think it's stuff I can handle. Thanks to all for input!