Excel, import data based on file date 1

[krappleby025]

Hi All,

i have a question, may be simple maybe not..
i have an excel spreadsheet, which i am tryint to use to create a report.

The sheet contains a cell with a date in it. in the format of 09.11.04 dd.mm.yy This cell is C4

Now, i have a load of files, each one labeled in the following manner

AU high usage report 09.11.04.xls
DE high usage report 09.11.04.xls

etc

each file is labeled with a different date.
now i am trying to set up the spreadsheet so that when i enter the date in C4 it pulls all the corresponding files into the excel sheet.

i set up a macro to do this, but i know there is another way.. but anyway, the macro at the moment just pulls in a set file. i am trying to make it pull in the file for the data entered in C4. looking into the macro i see this

"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=P:\New Reports\AU Credit Limit" _
, _
" High usage(""=C4"").xls;Mode=Share Deny Write;Extended

note that i have already tried to set it up so that the file that corresponds to the date is pulled but it does not seem to work.

Can someone tell me how to change the section
High usage(""=C4"").xls;Mode
so that the file that has the date entered in the C4 box is pulled

Cheers

 

[Molby]

Try:

"High usage " & Range("c4").Value & ".xls"

 

[krappleby025]

Thanks Molby however

now im getting an error on the following line

.Refresh BackgroundQuery:=False

any ideas

 

[Molby]

What's the error? And can you show the rest of your code.

 

[krappleby025]

Sub test()
'
' test Macro
' Macro recorded 9-11-2004 by defprof
'
' Keyboard Shortcut: Ctrl+t
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=P:\New Reports\AU Credit Limit" _
, _
"High usage" & Range("c4").Value & ".xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Pat" _
, _
"h="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=34;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops" _
, _
"=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encr" _
, _
"ypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fal" _
, "se"), Destination:=Range("C6"))
.CommandType = xlCmdTable
.CommandText = Array("'AU Credit Limit High usage$'")
.Name = "AU Credit Limit High usage " & Range("c4").Value & ".xls"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"P:\New Reports\AU Credit Limit High usage" & Range("c4").Value & ".xls"
.Refresh BackgroundQuery:=False
End With
End Sub

************************

That is the code

the error is

Run-time error 1004

The Microsoft het database engine could not find the object "AU Credit Limit High usage". make sure the object exists and that you spell its name and path name correctly.

when i click on debug

i end up at that above line, in yellow

 

[Molby]

It sounds like it can't find the file name you've given, make sure that you include any spaces in the name such as
AU Credit Limit High usage [red]_[/red]" & Range("c4").Value & ".xls"

 

[asrisk]

Try using the Format function to put your date into the right format (you have formatted the cell to display as dd.mm.yy, but Range("C4").value does not pick up this format)

"P:\New Reports\AU Credit Limit High usage _" & format(Range("c4").Value, "dd.mm.yy") & ".xls"

Hope this helps

Andy.


-------
I am not responsible for any "Sponsored Links" which may appear in my messages.

 

[asrisk]

Whoops, ignore the space and underscore afer "usage" above.

-------
I am not responsible for any "Sponsored Links" which may appear in my messages.