Search results

Yeah, when I post it like that in Excel it works fine. It's when I try to translate it from Access that it goes haywire. I tried running the string you posted in Immediate and it error'd out

I received the same error you did when I tested in Immediate

My apologies Public Function excelModify(fileName As String, sheetName As String) Dim xlApp As Excel.Application Dim xlWork As Excel.workbook Dim xlSheet As Excel.Worksheet Dim newPath As String, fileNameWOPath As String, fileNameWOExt As String, path As String, xlFormula As...

Sorry, I shortened the code. I do have as my declarations: Dim xlApp As Excel.Application Dim xlWork As Excel.workbook Dim xlSheet As Excel.Worksheet Dim xlFormula As String Set xlApp = CreateObject("Excel.Application") Set xlWork = xlApp.Workbooks.Open(fileName) Set xlSheet =...

I wrote the following formula in Excel originally in order to concatenate a bunch of data. =TEXTJOIN(";",TRUE,IF(A:A=A2,B:B,"")) I am trying to pass the formula into Excel from Access in order to run the concatenation on selected files. However I keep getting an "Application-defined or...

Okay I remembered why this happens. It's because of the CSV file saves as CSV UTF-8 instead of plain CSV.

I did think of that and tried deleting row 1, then inserting a new row1 and manually adding the headers back in. But it was the same result. I've struggled with these in the past and never found a solution, just came up with some workarounds. This time I'd like to figure out how to get rid of...

I have a spreadsheet report coming from an application. I do some VBA manipulation in Excel from Access to the file, then import the data to Access. One of the manipulation steps I have Access perform is to add in a column to the Excel file called "Key" (see code below). However, I get the...

I'm toying with an idea and wanted to see if there is any validity to it. If I had a function I wanted to run multiple times based on a user's MsgBox choice (yes or no), could I use a do until loop instead of hard-coding the function to run a hundred times over? Basically: Do Until MsgBox = 1...

That did the trick! I had tried something like that previously, but kept a join in the NOT IN statement. Thanks for your help as always!

I have the following tables (anonymized): Compare_App: Name John Smith Compare_Active: Name John Smith Compare_Terms: Name John Smith I am trying to write a multi-join query that says Select the names from "Compare_App" that also appear in "Compare_Terms" and exclude those...

Thank you! And I apologize I should have been clearer. I do have a backup copy, I just wasn't sure where to modify. This helps for sure!

I recently split a database for the first time to assist in its performance. I still have some changes I need to make to the VBA. How do I go about modifying the VBA of the split database? I have the front-end (.accde) that has the VBA but is inaccessible, and the back-end file doesn't appear to...

Lol For The Win!🏆

@Gasman FTW! Although, honestly everyone, thank you sooo much for sticking with me! I am self-taught with Access and VBA, and countless times you guys have saved me. I appreciate all the help given here today!

I tried with recordcount too, but the variant that was returned was not equaling the actual count (x = rs2.recordcount, x should = 4, but instead was =1 every time)

I did double-check for an empty value and there are none, but that's a good callout. When I create the recordset, I'll be sure to accommodate for that. I'll try to restate: I am building a database that our team uses to perform access reviews of certain applications. This is a version 2 of a...

X is apparently empty, but at the moment it should be 4. I tried modifying the x line to be (with no luck): X = rs2.getrows(rs2.fields("Paths"))

I took out the As Variant on the ReDim line, but same error

So I found a slightly simpler bit of code that worked on test, however when I ran it through the test I did "Dim vArray(4) as Variant" and did not include the "x" variable or the ReDim. Those 2 pieces I added when I switched back to my recordset to set a variable size array. Now I am getting a...