Access & Excel Gurus

General Computing Coding and Design
1

OK, using VBA I would like to be able to drop some data into an excel spread sheet, I’ll need to specify the document name and sheet name which I think I can do easily enough, but then I need to essentially do a Vlookup AND a Hlookup to find the cell ref where I need to drop the text.

I’ll try to explain in simple terms,
we generally use a spreadsheet as a diary / planner, a different workbook for each area, a different sheet for each month, list of names down the left on each sheet and dates across the top,

I’m trying to standardise the sheets (each area has their own creation) I can pull the data for names etc from my Database, and have set it all up to filter etc,
I want to be able to drop data into the sheet from the database against the right person and date (or date range)

If any one has any ideas then all help is appreciated, even if it’s a give up and go home fat boy!

Damski

2

Is the data coming from Access or is it all in Excel?

3

the data is in access going to excel, so the code will be run from access

4

So you have a big one here. I would not try it with a Spreadsheet if you’re hoping to link the data back to Access/MDB. The simple fact is that JET can handle the creation of the spreadsheets easy enough but linking the changes back will be maybe more then you can handle due to end-users borking the files up…

Try this maybe. Create the data back out to each user each month as a snapshot of data. Then create a import back into the main database based as temp tables, then update the data files points. If you do it like each CSV/XLS is stored in a IMPORT directory like c:\import\1 c:\import\2 you can simply sweep those directories calling each directory a TEMP table in the database.

Like this:

Use this FileSearch code to check the directory
Function FilesSearch(thepath As String, Ext As String)

Dim unkndir() As String
Dim tempdir As String
Dim ff As String
Dim DirCount As Integer
Dim X As Integer
DirCount = 0
ReDim unkndir(0) As String
unkndir(DirCount) = ""
If Right(thepath, 1) <> "\" Then
thepath = thepath & "\"
End If
DoEvents
tempdir = Dir(thepath, vbDirectory)
Do While tempdir <> ""
If tempdir <> "." And tempdir <> ".." Then
If (GetAttr(thepath & tempdir) And vbDirectory) = vbDirectory Then
unkndir(DirCount) = thepath & tempdir & "\"
DirCount = DirCount + 1
ReDim Preserve unkndir(DirCount) As String
End If
End If
tempdir = Dir
Loop
ff = Dir(thepath & Ext)
Do Until ff = ""
Found1 = thepath ' & ff
Found2 = ff

 ff = Dir
 
 Loop

 'searches through all sub direictories

 For X = 0 To (UBound(unkndir) - 1)
 FilesSearch unkndir(X), Ext
 Next X
 End Function

Then call it like:

Dim FF as String
DIRY as String
TABL as String
TABL =“TEMP NAME OF TABLE FOR THIS DIRECTORY”
DIRY = “c:\Import\1”
FF= “IMPORT.CSV”
Call FilesSearch(DIRY, ff)
If Len(Found2) > 4 Then
FileCopy Found1 & Found2, “c:\program\import.csv”

Then from here import that file to the temp table

Remember to Kill the FOUND1 & FOUND2 so that directory is empty for next time.

Use something like this for import

Set dbs = DBEngine.Workspaces(0).OpenDatabase(“c:\pathway\path_2000.mdb”, , False)

sSQL = “DELETE * from [Enter table name here]”
dbs.Execute sSQL

sSQL = “Insert INTO [Enter table name here] SELECT * FROM [Text;Database=c:\program;HDR=NO].[Import.csv]”
dbs.Execute sSQL
Then you step thru it all for updating tables other places… then loop around to do for each directory…

Seems like a lot but way faster then hoping to link data back…also the CSV files are smaller then XLS files…and you can have users that simply have older version, or generic Spreadsheets working…also works great for vendors also…

Just me 2 + cents !

5

Step I hadn’t even thought about data going back the other way, not that I was going to even try it but the idiots we have would either ‘assume’ that it can or make such a mess off it, I hadn’t also thought about the situation if data changes.

Sod it, they can do it themselves…

As I said, shut it fat boy way to big a bag of worms