Hi...I'm self taught on use of Access...comfortable with use of macro's but new to actually writing modules & VB code.
I'm designing DB to reconcile 100+ different group bills--the input files of which are on located in array of subfolder locations on various shared drives. Good news is that once Servicer identifies (via form) which Group(company) & Year & Month of bill they will be reconciling...I then know exactly what filename (and path) is which I need to import. But I can't figure out way to pass "Filename" into macro (which is ideally what I'd like to do)...and....what I've written up for VB doesn't work. It doesn't like my 'strPathFile' ---even though I've made sure I'm passing all the " , \ and file type.
Here's my simple Vb
Dim db1 as Object
Dim strHasFieldHeader as Variant
Dim strPathFile as Variant
Set db1 = CurrentDb.OpenRecordset("SELECT [Temp_Tbl_BILL_PERIOD].[PathFile] as strPathFile FROM [Temp_Tbl_BILL_PERIOD] ")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_INPUT", strPathFile, strHasFieldHeader
BACKGROUND:Two tables:
Tbl_COMPANY_LISTING: Name; InitialDirectory; FileType; HasFieldHeader; Slash
Temp_Tbl_BILL_PERIOD: Select_COMP; Select_YEAR, Select_MONTH, & PathFile
The 'PathFile' I build via Servicer input & sql update drawn from Tbl_COMPANY_LISTING = InitialDirectory + Select_YEAR + Slash + Select_MONTH + Slash + "BILL_INPUT + FileType
Never posted before so I'm not sure how much detail/background is needed. I want to avoid the "GetFileNameSave" thing...where servicer has to drill to the exact location and file....and my attempt at passing in a "forced" InitialDirectory (to at least get them to the subfolder of group they are working on) didn't work & couldn't figure out proper way.
I'm designing DB to reconcile 100+ different group bills--the input files of which are on located in array of subfolder locations on various shared drives. Good news is that once Servicer identifies (via form) which Group(company) & Year & Month of bill they will be reconciling...I then know exactly what filename (and path) is which I need to import. But I can't figure out way to pass "Filename" into macro (which is ideally what I'd like to do)...and....what I've written up for VB doesn't work. It doesn't like my 'strPathFile' ---even though I've made sure I'm passing all the " , \ and file type.
Here's my simple Vb
Dim db1 as Object
Dim strHasFieldHeader as Variant
Dim strPathFile as Variant
Set db1 = CurrentDb.OpenRecordset("SELECT [Temp_Tbl_BILL_PERIOD].[PathFile] as strPathFile FROM [Temp_Tbl_BILL_PERIOD] ")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Tbl_INPUT", strPathFile, strHasFieldHeader
BACKGROUND:Two tables:
Tbl_COMPANY_LISTING: Name; InitialDirectory; FileType; HasFieldHeader; Slash
Temp_Tbl_BILL_PERIOD: Select_COMP; Select_YEAR, Select_MONTH, & PathFile
The 'PathFile' I build via Servicer input & sql update drawn from Tbl_COMPANY_LISTING = InitialDirectory + Select_YEAR + Slash + Select_MONTH + Slash + "BILL_INPUT + FileType
Never posted before so I'm not sure how much detail/background is needed. I want to avoid the "GetFileNameSave" thing...where servicer has to drill to the exact location and file....and my attempt at passing in a "forced" InitialDirectory (to at least get them to the subfolder of group they are working on) didn't work & couldn't figure out proper way.
